<?php

namespace app\modules\Order\models;

use Yii;
use app\modules\Product\models\Product;
use app\modules\Order\models\OrderAssign;
use app\modules\Order\models\Order;
use app\modules\Order\models\Leader;
use app\modules\Product\models\ProductRoomPrice;
use app\modules\Product\models\ProductRoom;
use app\modules\Order\models\OrderRoomCustomer;
use app\modules\Order\models\OrderPrice;
use app\modules\Order\models\OrderPriceType;
use Overtrue\Pinyin\Pinyin;


class Excel extends \yii\db\ActiveRecord
{
    
    //模板 1 出境名单-北京
    public function Template1($product_id,$assign_id,$lead_id)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);
        $leader = Leader::findOne($lead_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('宋体');

        //设置宽度
        $abc_list = ['A','B','C','D','E','F','G','H','I','J'];
        foreach($abc_list as $abc_k){
            $sheet->getColumnDimension($abc_k)->setWidth(16);
        }
      
        $sheet->setCellValue("A1","组团社序号")
              ->setCellValue("D1","团队编号")
              ->setCellValue("E1",$product->code)
              ->setCellValue("G1","年份")
              ->setCellValue("A2","领队姓名")
              ->setCellValue("C2","领队证号")
              ->setCellValue("E2","领队手机号码")
              ->setCellValue("G2","条形码编号");  
        if(!empty($leader)){      
          $sheet->setCellValue("B2",$leader->first_name.$leader->last_name)
                ->setCellValue("D2",$leader->code)  
                ->setCellValue("F2",$leader->phone);
        }                 

        $titleArray = ['序号','中文','汉语拼音(姓)','汉语拼音(名)','性别','出生日期','出生地','护照号码','发证机关','发证日期'];
        $sheet->fromArray($titleArray, NULL, 'A3');

        $sheet->setCellValue("A4","领队");
        if(!empty($leader)){ 
          $sheet->setCellValue("B4",$leader->first_name.$leader->last_name)
                ->setCellValue("C4",$leader->en_first_name)
                ->setCellValue("D4",$leader->en_last_name)
                ->setCellValue("E4",yii::$app->params['gender'][$leader->gender])
                ->setCellValue("F4",$leader->birthday) 
                ->setCellValue("G4",$leader->native)
                ->setCellValue("H4",$leader->passport)
                ->setCellValue("I4",$leader->passport_add)
                ->setCellValue("J4",date('Y-m-d',strtotime($leader->passport_grant)));
        }
        $assign = OrderAssign::findOne($assign_id);

        $key = 0;

        foreach($assign->orders as $v){
            if($v->status <=4){
                //先找房间 再找人
                foreach($v->room as $room){
                        foreach($room->orderRoomCustomer as $val){   
                            $sheet->setCellValue(sprintf("A%d",$key+5),$key+1);
                            $sheet->setCellValue(sprintf("B%d",$key+5),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                            $sheet->setCellValue(sprintf("C%d",$key+5),$val->orderCustomer->en_first_name);
                            $sheet->setCellValue(sprintf("D%d",$key+5),$val->orderCustomer->en_last_name);
                            $sheet->setCellValue(sprintf("E%d",$key+5),yii::$app->params['gender'][$val->orderCustomer->gender]);
                            $sheet->setCellValue(sprintf("F%d",$key+5),$val->orderCustomer->birthday);
                            $sheet->setCellValue(sprintf("G%d",$key+5),$val->orderCustomer->native);
                            $sheet->setCellValue(sprintf("H%d",$key+5),$val->orderCustomer->passport);
                            $sheet->setCellValue(sprintf("I%d",$key+5),$val->orderCustomer->passport_add);
                            $sheet->setCellValue(sprintf("J%d",$key+5),$val->orderCustomer->passport_grant);
                            $key++;
                        }           
                }    
            }
            
        }

        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf('B4:J%s',$key+4))->applyFromArray($styleArray);

        $female_num = $man_num = 0;
        foreach($assign->orders as $v1){
            foreach($v1->customer as $v2){
                if($v2->gender == 2){
                   $female_num += 1;
                }else if($v2->gender == 1){
                   $man_num += 1;
                }
            }
        }

        $sheet->getStyle(sprintf("C%d:F%d",$key+6,$key+7))->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("C%d",$key+11))->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("E%d:E%d",$key+10,$key+11))->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');
        $sheet->getStyle(sprintf("G%d",$key+10))->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');

        $sheet->setCellValue(sprintf("A%d",$key+6),"出境时间")
              ->setCellValue(sprintf("B%d",$key+6),"")
              ->setCellValue(sprintf("C%d",$key+6),"由")
              ->setCellValue(sprintf("D%d",$key+6),"")
              ->setCellValue(sprintf("E%d",$key+6),"出境")
              ->setCellValue(sprintf("F%d",$key+6),"出境航班"); 

        $sheet->setCellValue(sprintf("A%d",$key+7),"入境时间")
              ->setCellValue(sprintf("B%d",$key+7),"")
              ->setCellValue(sprintf("C%d",$key+7),"由")
              ->setCellValue(sprintf("D%d",$key+7),"")
              ->setCellValue(sprintf("E%d",$key+7),"入境")
              ->setCellValue(sprintf("F%d",$key+7),"入境航班"); 

        $sheet->setCellValue(sprintf("A%d",$key+8),"总人数")
              ->setCellValue(sprintf("B%d",$key+8),$female_num+$man_num)
              ->setCellValue(sprintf("C%d",$key+8),"男性人数")
              ->setCellValue(sprintf("D%d",$key+8),$man_num)
              ->setCellValue(sprintf("E%d",$key+8),"女性人数")
              ->setCellValue(sprintf("F%d",$key+8),$female_num); 

        $sheet->setCellValue(sprintf("A%d",$key+9),"旅游线路名称");

        $sheet->setCellValue(sprintf("A%d",$key+10),"组团社名称")
              ->setCellValue(sprintf("B%d",$key+10),"")
              ->setCellValue(sprintf("C%d",$key+10),"组团社联络人")
              ->setCellValue(sprintf("D%d",$key+10),"")
              ->setCellValue(sprintf("E%d",$key+10),"电话")
              ->setCellValue(sprintf("F%d",$key+10),"")
              ->setCellValue(sprintf("G%d",$key+10),"联系人身份证号"); 

        $sheet->setCellValue(sprintf("A%d",$key+11),"接待社名称")
              ->setCellValue(sprintf("B%d",$key+11),"")
              ->setCellValue(sprintf("C%d",$key+11),"接待社联络人")
              ->setCellValue(sprintf("D%d",$key+11),"")
              ->setCellValue(sprintf("E%d",$key+11),"电话")
              ->setCellValue(sprintf("F%d",$key+11),""); 

        $sheet->setCellValue(sprintf("A%d",$key+12),"行程信息");

        $sheet->setCellValue(sprintf("A%d",$key+13),"前往地")
              ->setCellValue(sprintf("B%d",$key+13),"前往国家")
              ->setCellValue(sprintf("C%d",$key+13),"游览行程")
              ->setCellValue(sprintf("D%d",$key+13),"是否过境")
              ->setCellValue(sprintf("E%d",$key+13),"天数")
              ->setCellValue(sprintf("F%d",$key+13),"站点"); 
          
        $sheet->setAutoFilter('A3:J3'); //设置筛选

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $leader_name = '';

        if(!empty($leader)){
           $leader_name = sprintf("%s%s",$leader->first_name,$leader->last_name);
        }
        
        $down_name = sprintf("%s_%s_%s_%s",$product->name,$product->go_time,$assign->name,yii::$app->params['template'][1]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'assign_name' => $assign->name,
                'leader_name' => $leader_name,
                'down_name' => $down_name,
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 2 出境名单-天津
    public function Template2($product_id,$assign_id,$lead_id)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $leader = Leader::findOne($lead_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(12)->setName('宋体')->getColor()->setARGB('000000');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        $sheet->getColumnDimension('A')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(16);// 设置宽度       

        $titleArray = ['姓名','拼音名','性别','生日','出生地','手机','证照类型','证照号','签发地','发证日期'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        $sheet->getStyle("A1:K1")->getFont()->setSize(12)->setName('宋体')->getColor()->setARGB('FF0000');

        if(!empty($leader)){ 
          $sheet->setCellValue("A2",$leader->first_name.$leader->last_name)
                ->setCellValue("B2",$leader->en_first_name.'/'.$leader->en_last_name)
                ->setCellValue("C2",yii::$app->params['gender'][$leader->gender])
                ->setCellValue("D2",$leader->birthday)
                ->setCellValue("E2",$leader->native)
                ->setCellValue("F2",$leader->phone) 
                ->setCellValue("G2","护照")
                ->setCellValue("H2",$leader->passport)
                ->setCellValue("I2",$leader->passport_add)
                ->setCellValue("J2",date('Y-m-d',strtotime($leader->passport_grant)));
        }
        $sheet->setCellValue("k2","领队");

        $assign = OrderAssign::findOne($assign_id);

        $key = 0;

        foreach($assign->orders as $v){
            if($v->status <=4){
                //先找房间 再找人
                foreach($v->room as $room){
                    //if($room->stock == 1){ //计入库存
                         foreach($room->orderRoomCustomer as $val){   
                            $sheet->setCellValue(sprintf("A%d",$key+3),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                            $sheet->setCellValue(sprintf("B%d",$key+3),$val->orderCustomer->en_first_name.'/'.$val->orderCustomer->en_last_name);
                            $sheet->setCellValue(sprintf("C%d",$key+3),yii::$app->params['gender'][$val->orderCustomer->gender]);
                            $sheet->setCellValue(sprintf("D%d",$key+3),$val->orderCustomer->birthday);
                            $sheet->setCellValue(sprintf("E%d",$key+3),$val->orderCustomer->native);
                            $sheet->setCellValue(sprintf("F%d",$key+3),$v->phone);
                            $sheet->setCellValue(sprintf("G%d",$key+3),'护照');
                            $sheet->setCellValue(sprintf("H%d",$key+3),$val->orderCustomer->passport);
                            $sheet->setCellValue(sprintf("I%d",$key+3),$val->orderCustomer->passport_add);
                            $sheet->setCellValue(sprintf("J%d",$key+3),$val->orderCustomer->passport_grant);
                            $sheet->setCellValue(sprintf("K%d",$key+3),$key+1);
                            $key++;
                        }       

                }    
            }
        }

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);


       $leader_name = '';

        if(!empty($leader)){
           $leader_name = sprintf("%s%s",$leader->first_name,$leader->last_name);
        }
        
        $down_name = sprintf("%s_%s_%s_%s",$product->name,$product->go_time,$assign->name,yii::$app->params['template'][2]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'assign_name' => $assign->name,
                'leader_name' => $leader_name,
                'down_name' => $down_name,
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 3 歌诗达&地中海
    public function Template3($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(16);

        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:Z1')->applyFromArray($styleArray);

        $sheet->getColumnDimension('A')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(12);// 设置宽度
        $sheet->getRowDimension(1)->setRowHeight(30);    

        $titleArray = ['CRUISE ID','舱型','舱号','最大入住人数','团号','姓','名','性别','生日','国籍','护照号码','签发日期','有效日期','NATIONALITY ID','LANGUUAGE','出生地','签发地','HOME CITY','HOME STATE','HOME COUNTRY','DINING choice （用餐批次，M/L）','中文名','旅行社紧急联系人','紧急联系方式','床型','分餐'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        
        $sheet->getStyle("A1:Z1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:Z1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('A5A5A5');
        $sheet->getStyle('A1:Z1')->getFont()->setSize(12)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);

        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }      
        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_3 = yii::$app->params['area']['country_code_3'];
        $key = 0;

        foreach($orders as $v){
            
            //先找房间 再找人
            foreach($v->room as $room){
                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){ 
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true); 
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";
                    } 
                    foreach($room->orderRoomCustomer as $val){   
                        $birthday = !empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'';
                        $sheet->setCellValue(sprintf("A%d",$key+2),"");
                        $sheet->setCellValue(sprintf("B%d",$key+2),$room_name);
                        $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValue(sprintf("D%d",$key+2),$room_num);
                        $sheet->setCellValue(sprintf("E%d",$key+2),($v->assign)?$v->assign->name:'');
                        $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_first_name);
                        $sheet->setCellValue(sprintf("G%d",$key+2),$val->orderCustomer->en_last_name);
                        $sheet->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender_en'][$val->orderCustomer->gender]);
                        $sheet->setCellValue(sprintf("I%d",$key+2),$birthday);
                        $sheet->setCellValue(sprintf("J%d",$key+2),!empty($country_code_3[$val->orderCustomer->country])?$country_code_3[$val->orderCustomer->country]:$val->orderCustomer->country);
                        $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport);
                        $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                        $sheet->setCellValue(sprintf("M%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                        $sheet->setCellValue(sprintf("N%d",$key+2),"");
                        $sheet->setCellValue(sprintf("O%d",$key+2),"");
                        $sheet->setCellValue(sprintf("P%d",$key+2),(!empty($province[$val->orderCustomer->native]))?strtoupper($province[$val->orderCustomer->native]) : $val->orderCustomer->native);
                        $sheet->setCellValue(sprintf("Q%d",$key+2),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : $val->orderCustomer->passport_add);
                        $sheet->setCellValue(sprintf("R%d",$key+2),"");
                        $sheet->setCellValue(sprintf("S%d",$key+2),"");
                        $sheet->setCellValue(sprintf("T%d",$key+2),"");
                        $sheet->setCellValue(sprintf("U%d",$key+2),"");
                        $sheet->setCellValue(sprintf("V%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                        $sheet->setCellValue(sprintf("W%d",$key+2),$v->username);
                        $sheet->setCellValueExplicit(sprintf("X%d",$key+2),$v->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValue(sprintf("Y%d",$key+2),"双床");
                        $sheet->setCellValue(sprintf("Z%d",$key+2),"");
                        $key++;
                    }       

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   
                                    $birthday = !empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'';
                                    $sheet->setCellValue(sprintf("A%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("B%d",$key+2),$room_name);
                                    $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                                    $sheet->setCellValue(sprintf("D%d",$key+2),$room_num);
                                    $sheet->setCellValue(sprintf("E%d",$key+2),isset($merge_room->order->assign)?$merge_room->order->assign->name:'');
                                    $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_first_name);
                                    $sheet->setCellValue(sprintf("G%d",$key+2),$val->orderCustomer->en_last_name);
                                    $sheet->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender_en'][$val->orderCustomer->gender]);
                                    $sheet->setCellValue(sprintf("I%d",$key+2),$birthday);
                                    $sheet->setCellValue(sprintf("J%d",$key+2),!empty($country_code_3[$val->orderCustomer->country])?$country_code_3[$val->orderCustomer->country]:$val->orderCustomer->country);
                                    $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport);
                                    $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                                    $sheet->setCellValue(sprintf("M%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                                    $sheet->setCellValue(sprintf("N%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("O%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("P%d",$key+2),(!empty($province[$val->orderCustomer->native]))?strtoupper($province[$val->orderCustomer->native]) : $val->orderCustomer->native);
                                    $sheet->setCellValue(sprintf("Q%d",$key+2),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : $val->orderCustomer->passport_add);
                                    $sheet->setCellValue(sprintf("R%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("S%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("T%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("U%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("V%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                                    $sheet->setCellValue(sprintf("W%d",$key+2),$v->username);
                                    $sheet->setCellValueExplicit(sprintf("X%d",$key+2),$v->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
                                    $sheet->setCellValue(sprintf("Y%d",$key+2),"双床");
                                    $sheet->setCellValue(sprintf("Z%d",$key+2),"");
                                    $key++;
                                }     

                        }
                    }


                }    
            }    
          
        }

        $sheet->getStyle(sprintf('A1:Z%d',$key+1))->applyFromArray($styleArray);
      
        $sheet->setAutoFilter("A1:Z1");

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][3]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;

    }

    //模板 4 皇家海外名单
    public function Template4($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial Unicode MS');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(20);
        $sheet->getColumnDimension('A')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(12);// 设置宽度

        $sheet->getStyle('A:C')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('F:G')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('J:K')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('R:S')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('V:W')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('AD:AE')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AH:AI')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('AP:AQ')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('AT:AU')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getColumnDimension('F')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('V')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('W')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AD')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AE')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AH')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AI')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AP')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AQ')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AT')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AU')->setWidth(20);// 设置宽度
        $titleArray = ['订单号','团队号','舱型','等级','船舱号码','第一个人的姓','第一个客人的名','第一个客人的用餐批次','这个部分只放C/O','第一个客人的国籍','第一个客人的生日','第一个客人的年龄（不用填写） ',' 年龄范围（不用填写）','第一个客人的性别','第一个客人的护照号','护照有效期','紧急联系人号码',' 第二个人的姓','第二个客人的名','第二个客人的用餐批次','这个部分只放C/O','第二个客人的国籍',' 第二个客人的生日','第二个客人的年龄（不用填写）','  年龄范围（不用填写）','第二个人客人的性别 ','第二个客人的护照号 ','护照有效期 ','紧急联系人号码',' 第三个人的姓  ','第三个客人的名 ','第三个客人的用餐批次 ',' 这个部分只放C/O ','第三个客人的国籍  ','第三个客人的生日','第三个客人的年龄（不用填写）','  年龄范围（不用填写）','  第三个人客人的性别',' 第三个客人的护照号',' 护照有效期 ','紧急联系人号码 ','第四个人的姓','  第四个客人的名 ','第四个客人的用餐批次 ',' 这个部分只放C/O ','第四个客人的国籍 ',' 第四个客人的生日 ',' 第四个客人的年龄（不用填写） ',' 年龄范围（不用填写）','  第四个人客人的性别 ','第四个客人的护照号',' 护照有效期 ','紧急联系人号码'];

        $sheet->fromArray($titleArray, NULL, 'A1');

        $titleArray2 = ['ORDER_ID','GROUP_ID','OCCUPANCY',' CATEGORY ',' CABIN ','GUEST_1_LAST_NAME ','GUEST_1_FIRST_NAME','  GUEST_1_DINING ',' GUEST_1_AIR ','GUEST_1_CTZ',' GUEST_1_DOB ','GUEST_1_AGE ','GUEST_1_AGE_RANGE',' GUEST_1_GENDER','  GUEST_1_PASSPORTNUMBER','  GUEST_1_EXPIRYDATE ',' GUEST_1_EMERGENCYCONTACTNUMBER ',' GUEST_2_LAST_NAME ','GUEST_2_FIRST_NAME ',' GUEST_2_DINING ',' GUEST_2_AIR ','GUEST_2_CTZ',' GUEST_2_DOB ','GUEST_2_AGE',' GUEST_2_AGE_RANGE',' GUEST_2_GENDER','  GUEST_2_PASSPORTNUMBER ',' GUEST_2_EXPIRYDATE ',' GUEST_2_EMERGENCYCONTACTNUMBER','  GUEST_3_LAST_NAME',' GUEST_3_FIRST_NAME ',' GUEST_3_DINING','  GUEST_3_AIR ','GUEST_3_CTZ ','GUEST_3_DOB ','GUEST_3_AGE',' GUEST_3_AGE_RANGE ','GUEST_3_GENDER ',' GUEST_3_PASSPORTNUMBER','  GUEST_3_EXPIRYDATE ',' GUEST_3_EMERGENCYCONTACTNUMBER ',' GUEST_4_LAST_NAME',' GUEST_4_FIRST_NAME ',' GUEST_4_DINING ',' GUEST_4_AIR',' GUEST_4_CTZ',' GUEST_4_DOB',' GUEST_4_AGE ','GUEST_4_AGE_RANGE',' GUEST_4_GENDER ',' GUEST_4_PASSPORTNUMBER ',' GUEST_4_EXPIRYDATE','  GUEST_4_EMERGENCYCONTACTNUMBER'];
        $sheet->fromArray($titleArray2, NULL, 'A2');

        if($type == 3){ //全部订单
          $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
          $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
          $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }          

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province); 
        $province = array_merge($province,yii::$app->params['area']['country_english']); 
        $country_code_3 = yii::$app->params['area']['country_code_3'];

        $key = 0;

        foreach($orders as $v){

            if($v->product->stock == 1){ // 国内航线
                foreach($v->room as $room){  
                    if($room->stock == 1){ //计入库存
                        $sheet->setCellValueExplicit(sprintf("A%d",$key+3),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        //团号
                       $assign_names = !empty($v->assign)?$v->assign->name:'';
                       if($room->merge_id){
                          $merge_room = $room->mergeRoom;
                          $assign_names .= '/';
                          $assign_names .= isset($merge_room->order->assign->name)?$merge_room->order->assign->name:'';
                       }
                       $sheet->setCellValue(sprintf("B%d",$key+3),$assign_names); 
                      
                        $room_name = $room->productFinalRoom->room->name.$room->productFinalRoom->room->code;
                        $room_code = $room->code;
                        $room_num = $room->final_num.'人';

                        $sheet->setCellValue(sprintf("C%d",$key+3),$room_name);
                        $sheet->setCellValue(sprintf("D%d",$key+3),$room_num);
                        $sheet->setCellValue(sprintf("E%d",$key+3),$room_code);

                        $customer_msg_arr = [];
                        //$customers = $v->getRoomCustomer($room->id)->all();
                        $customers = $room->orderRoomCustomer;
                        if(!empty($customers)){
                            foreach($customers as $cust){
                                if($cust->orderCustomer->status == 1){
                                  $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                  $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                  $customer_msg_arr[] = "";
                                  $customer_msg_arr[] = "C/O";
                                  $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                  $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                  $customer_msg_arr[] = "";
                                  $customer_msg_arr[] = "";
                                  $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                  $customer_msg_arr[] = $cust->orderCustomer->passport;
                                  $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                  $customer_msg_arr[] = sprintf("%s",$v->phone);
                                }  
                            }  
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            $customers_merge = $merge_room->orderRoomCustomer;
                                if($customers_merge){
                                    foreach($customers_merge as $cust){   
                                        if($cust->orderCustomer->status == 1){
                                          $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                          $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = "C/O";
                                          $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                          $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                          $customer_msg_arr[] = $cust->orderCustomer->passport;
                                          $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                          $customer_msg_arr[] = sprintf("%s",$v->phone);
                                        }  
                                    }     

                                }
                        }

                        $sheet->fromArray($customer_msg_arr, NULL, sprintf("F%d",$key+3));
                        $key++;
                    }   
                }
            }elseif($v->product->stock == 2){ // 海外航线
                $t = json_decode($v->product->rooms, true); 
                foreach($v->room as $room){ // orderRoom
                    if($room->stock == 1){ //计入库存
                       $sheet->setCellValueExplicit(sprintf("A%d",$key+3),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                       //团号
                       $assign_names = !empty($v->assign)?$v->assign->name:'';
                       if($room->merge_id){
                          $merge_room = $room->mergeRoom;
                          $assign_names .= '/';
                          $assign_names .= isset($merge_room->order->assign->name)?$merge_room->order->assign->name:'';
                       }
                       $sheet->setCellValue(sprintf("B%d",$key+3),$assign_names);

                        //$room_name = $t[$room->room_id]['name'];
                       $room_name = $t[$room->final_room_id]['name'];
                       $room_code = $room->code;
                        //$room_num =  $room->num.'人';
                       $room_num =  $room->final_num.'人';

                        $sheet->setCellValue(sprintf("C%d",$key+3),$room_name);
                        $sheet->setCellValue(sprintf("D%d",$key+3),$room_num);
                        $sheet->setCellValue(sprintf("E%d",$key+3),$room_code);
                        $customer_msg_arr = [];
                        //$customers = $v->getRoomCustomer($room->id)->all();
                        $customers = $room->orderRoomCustomer;
                        if(!empty($customers)){
                            foreach($customers as $cust){
                                if($cust->orderCustomer->status == 1){
                                    $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                    $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                    $customer_msg_arr[] = "";
                                    $customer_msg_arr[] = "C/O";
                                    $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                    $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                    $customer_msg_arr[] = "";
                                    $customer_msg_arr[] = "";
                                    $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                    $customer_msg_arr[] = $cust->orderCustomer->passport;
                                    $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                    $customer_msg_arr[] = sprintf("%s",$v->phone);
                                }    
                            }  
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            $customers_merge = $merge_room->orderRoomCustomer;
                            if($customers_merge){
                                foreach($customers_merge as $cust){   
                                    if($cust->orderCustomer->status == 1){
                                      $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                      $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                      $customer_msg_arr[] = "";
                                      $customer_msg_arr[] = "C/O";
                                      $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                      $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                      $customer_msg_arr[] = "";
                                      $customer_msg_arr[] = "";
                                      $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                      $customer_msg_arr[] = $cust->orderCustomer->passport;
                                      $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                      $customer_msg_arr[] = sprintf("%s",$v->phone);
                                    }  
                                }     

                            }
                        }
                        $sheet->fromArray($customer_msg_arr, NULL, sprintf("F%d",$key+3));
                        $key++;
                    }    
                }

                  
            }  
           
        }


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
       
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][4]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 5 皇家母港名单
    public function Template5($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setBold(true)->setName('Trebuchet MS');
        $sheet->getDefaultStyle()->getFont()->getColor()->setARGB('333333');
        $sheet->getDefaultColumnDimension()->setWidth(16);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:CF2')->applyFromArray($styleArray);
        $sheet->getColumnDimension('A')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(16);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(30);
        $sheet->getRowDimension(2)->setRowHeight(20);   
        $sheet->getStyle('D')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('G:H')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('K:M')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('S')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('U:X')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AA:AB')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AE:AG')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AM')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');    
        $sheet->getStyle('AO:AR')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');      
        $sheet->getStyle('AU:AV')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('AY:BA')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BG')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BI:BL')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BO:BP')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BS:BU')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('CA')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('CC:CE')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');

        $titleArray = ['入住人数 ','船舱类型 ',' 船舱号码  ','第一个客人称谓的英文  ','第一个客人姓的拼音 ','第一个客人名的拼音 ','第一个客人中间名的拼音 ','第一个客人的中文称谓','  第一个客人姓的中文',' 第一个客人名的中文',' 第一个客人中间名的中文',' 第一个客人所在省份的中文 ',' 第一个客人所在省份的拼音','第一个客人的身份证号码','第一个客人的身份证有效期','第一个客人出生省份的拼音','  第一个客人的用餐批次','  第一个客人的船票类型(只放C/O)',' 第一个客人的国籍','  第一个客人的生日','  第一个客人的性别','  研讨会编号',' 领队 ',' 第一个客人的护照号',' 第一个客人的护照有效期 ','紧急联系人号码 ','第二个客人称谓的英文 ',' 第二个客人姓的拼音',' 第二个客人名的拼音',' 第二个客人中间名的拼音',' 第二个客人的中文称谓 ',' 第二个客人姓的中文',' 第二个客人名的中文',' 第二个客人中间名的中文',' 第二个客人所在省份的中文 ',' 第二个客人所在省份的拼音','第二个客人的身份证号码','第二个客人的身份证有效期','第二个客人出生省份的拼音','  第二个客人的用餐批次 ',' 第二个客人的船票类型(只放C/O) ','第二个客人的国籍 ',' 第二个客人的生日  ','第二个人客人的性别',' 研讨会编号',' 领队','  第二个客人的护照号',' 护照有效期 ','紧急联系人号码 ','第三个客人称谓的英文 ',' 第三个客人姓的拼音',' 第三个客人名的拼音 ','第三个客人中间名的拼音',' 第三个客人的中文称谓 ',' 第三个客人姓的中文',' 第三个客人名的中文',' 第三个客人中间名的中文 ','第三个客人所在省份的中文 ',' 第三个客人所在省份的拼音','第三个客人的身份证号码','第三个客人的身份证有效期','第三个客人出生省份的拼音','  第三个客人的用餐批次 ',' 第三个客人的船票类型(只放C/O) ','第三个客人的国籍','  第三个客人的生日 ',' 第三个人客人的性别',' 研讨会编号',' 领队 ',' 第三个客人的护照号 ','护照有效期','紧急联系人号码 ','第四个客人称谓的英文',' 第四个客人姓的拼音',' 第四个客人名的拼音 ','第四个客人中间名的拼音',' 第四个客人的中文称谓 ',' 第四个客人姓的中文',' 第四个客人名的中文',' 第四个客人中间名的中文',' 第四个客人所在省份的中文','  第四个客人所在省份的拼音','第四个客人的身份证号码','第四个客人的身份证有效期','第四个客人出生省份的拼音','  第四个客人的用餐批次  ','第四个客人的船票类型(只放C/O) ','第四个客人的国籍 ',' 第四个客人的生日','  第四个人客人的性别',' 研讨会编号',' 领队 ',' 第四个客人的护照号',' 护照有效期 ','紧急联系人号码',' 1'];
        $sheet->fromArray($titleArray, NULL, 'A1');
        $sheet->getStyle('A1:CF1')->getFont()->setSize(8)->setName('宋体');

        $titleArray1 = ['OCCUPANCY ',' CATEGORY  ',' CABIN ','  TITLE1 ',' PAX1_LAST_NAME ',' PAX1_FIRST_NAME',' PAX1_MIDDLE_NAME  ','  TITLE1_CHN ',' PAX1_LAST_NAME_CHN','  PAX1_FIRST_NAME_CHN ','PAX1_MIDDLE_NAME_CHN ','   PAX1_PROVINCE_CHN ','  PAX1_PROVINCE','PAX1_NATIONAL_ID','PAX1_NATIONAL_ID_EXP','PAX1_BIRTH_PROVINCE','PAX1_DINING',' PAX1_AIR ','   PAX1_CTZ  ','  PAX1_DOB  ','  PAX1_GENDER ','PAX1_CHARTER # ',' PAX1_LEADER',' PAX1_PASSPORT # ','PAX1_EXPIRY DATE',' PAX1_EMERGENCYPHONE','  TITLE2 ',' PAX2_LAST_NAME  ','PAX2_FIRST_NAME ','PAX2_MIDDLE_NAME  ','  TITLE2_CHN  ','PAX2_LAST_NAME_CHN ',' PAX2_FIRST_NAME_CHN ','PAX2_MIDDLE_NAME_CHN ','   PAX2_PROVINCE_CHN  ',' PAX2_PROVINCE ','PAX2_NATIONAL_ID','PAX2_NATIONAL_ID_EXP','PAX2_BIRTH_PROVINCE','  PAX2_DINING',' PAX2_AIR ','   PAX2_CTZ  ','  PAX2_DOB ','   PAX2_GENDER ','PAX2_CHARTER #  ','PAX2_LEADER ','PAX2_PASSPORT # ','PAX2_EXPIRYDATE ','   PAX2_EMERGENCYPHONE  ','  TITLE3 ',' PAX3_LAST_NAME ',' PAX3_FIRST_NAME',' PAX3_MIDDLE_NAME ','   TITLE3_CHN ',' PAX3_LAST_NAME_CHN ',' PAX3_FIRST_NAME_CHN ','PAX3_MIDDLE_NAME_CHN  ','  PAX3_PROVINCE_CHN ','  PAX3_PROVINCE','PAX3_NATIONAL_ID','PAX3_NATIONAL_ID_EXP','PAX3_BIRTH_PROVINCE','   PAX3_DINING ','PAX3_AIR  ','  PAX3_CTZ ','   PAX3_DOB ','   PAX3_GENDER',' PAX3_CHARTER # ',' PAX3_LEADER ','PAX3_PASSPORT # ','PAX3_EXPIRYDATE ','   PAX3_EMERGENCYPHONE ','   TITLE4  ','PAX4_LAST_NAME ',' PAX4_FIRST_NAME',' PAX4_MIDDLE_NAME ','   TITLE4_CHN','  PAX4_LAST_NAME_CHN','  PAX4_FIRST_NAME_CHN ','PAX4_MIDDLE_NAME_CHN ','   PAX4_PROVINCE_CHN  ',' PAX4_PROVINCE','PAX4_NATIONAL_ID','PAX4_NATIONAL_ID_EXP','PAX4_BIRTH_PROVINCE','   PAX4_DINING ','PAX4_AIR  ','  PAX4_CTZ','    PAX4_DOB ','   PAX4_GENDER ','PAX4_CHARTER #  ','PAX4_LEADER ','PAX4_PASSPORT # ','PAX4_EXPIRYDATE ','   PAX4_EMERGENCYPHONE  ','  Edited'];
        $sheet->fromArray($titleArray1, NULL, 'A2');
        $sheet->getStyle('A2:CF2')->getFont()->setSize(10)->setName('Arial');
        $sheet->getStyle('A2:CF2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');
     
        $sheet->freezePane('D3');
        $sheet->setAutoFilter('A2:CF2');

        if($type == 3){ //全部订单
          $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
          $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
          $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
          $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }          

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province); 
        $province = array_merge($province,yii::$app->params['area']['country_english']);     
        $country_code_3 = yii::$app->params['area']['country_code_3'];

        $key = 0;

        foreach($orders as $v){
            if($v->product->stock == 1){ // 国内航线
                foreach($v->room as $room){ 
                  if($room->stock == 1){ //计入库存  
//                      $sheet->setCellValueExplicit(sprintf("A%d",$key+3),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                      //团号
                       $assign_names = !empty($v->assign)?$v->assign->name:'';
                       if($room->merge_id){
                          $merge_room = $room->mergeRoom;
                          $assign_names .= '/';
                          $assign_names .= isset($merge_room->order->assign->name)?$merge_room->order->assign->name:'';
                       }
//                       $sheet->setCellValue(sprintf("B%d",$key+3),$assign_names);

                      $room_name = $room->productFinalRoom->room->name;
                      $room_code = $room->productFinalRoom->room->code;
                      $room_num = $room->final_num.'人';

                      $order_code = $v->code;
                      $sheet->setCellValue(sprintf("A%d",$key+3),$room_num);
                      $sheet->setCellValue(sprintf("B%d",$key+3),$room_name.$room_code);
                      //$sheet->setCellValueExplicit(sprintf("C%d",$key+3),$order_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit(sprintf("C%d",$key+3),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);

                      $customer_msg_arr = [];
                      //$customers = $v->getRoomCustomer($room->id)->all();
                      $customers = $room->orderRoomCustomer;
                      if(!empty($customers)){
                          foreach($customers as $cust){
                            if($cust->orderCustomer->status == 1){
                                $customer_msg_arr[] = "";
                                $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                $customer_msg_arr[] = "";
                                $customer_msg_arr[] = "";
                                $customer_msg_arr[] = $cust->orderCustomer->first_name;
                                $customer_msg_arr[] = $cust->orderCustomer->last_name;
                                $customer_msg_arr[] = "";
                                $customer_msg_arr[] = $cust->orderCustomer->native;
                                $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                $customer_msg_arr[] = $cust->orderCustomer->card;
                                $customer_msg_arr[] = "";
                                $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                $customer_msg_arr[] = "17:00";
                                $customer_msg_arr[] = "C/O";
                                $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                $customer_msg_arr[] = "";
                                $customer_msg_arr[] = "N";
                                $customer_msg_arr[] = $cust->orderCustomer->passport;
                                $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                $customer_msg_arr[] = $v->phone;
                            }  
                          }  
                      }
                      if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            $customers_merge = $merge_room->orderRoomCustomer;
                            if($customers_merge){
                                foreach($customers_merge as $cust){   
                                    if($cust->orderCustomer->status == 1){
                                          $customer_msg_arr[] = "";  
                                          $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                          $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = $cust->orderCustomer->first_name;
                                          $customer_msg_arr[] = $cust->orderCustomer->last_name;
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = $cust->orderCustomer->native;
                                          $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                          $customer_msg_arr[] = $cust->orderCustomer->card;
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                          $customer_msg_arr[] = "17:00";
                                          $customer_msg_arr[] = "C/O";
                                          $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                          $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                          $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                          $customer_msg_arr[] = "";
                                          $customer_msg_arr[] = "N";
                                          $customer_msg_arr[] = $cust->orderCustomer->passport;
                                          $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                          $customer_msg_arr[] = $v->phone;
                                    }  
                                }     

                            }
                      }
                      $sheet->fromArray($customer_msg_arr, NULL, sprintf("D%d",$key+3));
                      $key++;
                    }  
               }
            }elseif($v->product->stock == 2){ // 海外航线
                  $t = json_decode($v->product->rooms, true); 
                  foreach($v->room as $room){ // orderRoom
                    if($room->stock == 1){ //计入库存
//                        $sheet->setCellValueExplicit(sprintf("A%d",$key+3),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        //团号
                       $assign_names = !empty($v->assign)?$v->assign->name:'';
                       if($room->merge_id){
                          $merge_room = $room->mergeRoom;
                          $assign_names .= '/';
                          $assign_names .= isset($merge_room->order->assign->name)?$merge_room->order->assign->name:'';
                       }
//                       $sheet->setCellValue(sprintf("B%d",$key+3),$assign_names);
                        $room_name = $t[$room->final_room_id]['name'];
                        $room_num = $room->final_num.'人';
                        $order_code = $v->code;
                        $sheet->setCellValue(sprintf("A%d",$key+3),$room_num);
                        $sheet->setCellValue(sprintf("B%d",$key+3),$room_name);
                        //$sheet->setCellValueExplicit(sprintf("C%d",$key+3),$order_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValueExplicit(sprintf("C%d",$key+3),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $customer_msg_arr = [];
                        //$customers = $v->getRoomCustomer($room->id)->all();
                        $customers = $room->orderRoomCustomer;
                        if(!empty($customers)){
                            foreach($customers as $cust){
                             if($cust->orderCustomer->status == 1){
                                 $customer_msg_arr[] = "";
                                 $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                 $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                 $customer_msg_arr[] = "";
                                 $customer_msg_arr[] = "";
                                 $customer_msg_arr[] = $cust->orderCustomer->first_name;
                                 $customer_msg_arr[] = $cust->orderCustomer->last_name;
                                 $customer_msg_arr[] = "";
                                 $customer_msg_arr[] = $cust->orderCustomer->native;
                                 $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                 $customer_msg_arr[] = $cust->orderCustomer->card;
                                 $customer_msg_arr[] = "";
                                 $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                 $customer_msg_arr[] = "17:00";
                                 $customer_msg_arr[] = "C/O";
                                 $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                 $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                 $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                 $customer_msg_arr[] = "";
                                 $customer_msg_arr[] = "N";
                                 $customer_msg_arr[] = $cust->orderCustomer->passport;
                                 $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                 $customer_msg_arr[] = $v->phone;
                             } 
                            }  
                        }
                        if($room->merge_id){ //拼房房间的人
                            $merge_room = $room->mergeRoom;
                            $customers_merge = $merge_room->orderRoomCustomer;
                            if($customers_merge){
                                foreach($customers_merge as $cust){   
                                    if($cust->orderCustomer->status == 1){
                                        $customer_msg_arr[] = "";
                                        $customer_msg_arr[] = $cust->orderCustomer->en_first_name;
                                        $customer_msg_arr[] = $cust->orderCustomer->en_last_name;
                                        $customer_msg_arr[] = "";
                                        $customer_msg_arr[] = "";
                                        $customer_msg_arr[] = $cust->orderCustomer->first_name;
                                        $customer_msg_arr[] = $cust->orderCustomer->last_name;
                                        $customer_msg_arr[] = "";
                                        $customer_msg_arr[] = $cust->orderCustomer->native;
                                        $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                        $customer_msg_arr[] = $cust->orderCustomer->card;
                                        $customer_msg_arr[] = "";
                                        $customer_msg_arr[] = (!empty($province[$cust->orderCustomer->native]))?strtoupper($province[$cust->orderCustomer->native]) : $cust->orderCustomer->native;
                                        $customer_msg_arr[] = "17:00";
                                        $customer_msg_arr[] = "C/O";
                                        $customer_msg_arr[] = !empty($country_code_3[$cust->orderCustomer->country])?$country_code_3[$cust->orderCustomer->country]:$cust->orderCustomer->country;
                                        $customer_msg_arr[] = !empty($cust->orderCustomer->birthday) ? date("Y/m/d",strtotime($cust->orderCustomer->birthday)) :'';
                                        $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->orderCustomer->gender];
                                        $customer_msg_arr[] = "";
                                        $customer_msg_arr[] = "N";
                                        $customer_msg_arr[] = $cust->orderCustomer->passport;
                                        $customer_msg_arr[] = !empty($cust->orderCustomer->passport_date) ? date("Y/m/d",strtotime($cust->orderCustomer->passport_date)) :'';
                                        $customer_msg_arr[] = $v->phone;
                                    }  
                                }     

                            }
                        }
                        $sheet->fromArray($customer_msg_arr, NULL, sprintf("D%d",$key+3));
                        $key++;
                    }    
                  }   
                  
            }  
           
        }


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][5]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;

    }

    //模板 6 NCL名单
    public function Template6($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial');
        $sheet->getDefaultColumnDimension()->setWidth(16);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:P1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:P1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(16);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(50);       

        $titleArray = ['航程日期 Sailing Date (DD/MM/YYYY)','团队号 Group ID','订单号 Order ID','房型 CATEGORY','房间号 CABIN','姓(拼音) LAST NAME','名 (拼音) FIRST NAME','姓 (中文) LAST NAME (Mandarin)','名 (中文) FIRST NAME(Mandarin)','性别SEX(F/M)','国籍 NATIONALITY(use Country Codes Table)','出生日期(日/月/年) BIRTH DATE (DD/MM/YYYY)','护照号码 PASSPORT NUMBER','签发日期 (日/月/年) ISSUE DATE (DD/MM/YYYY)','有效期 (日/月/年) EXPIRE DATE(DD/MM/YYYY)','护照签发地PASSPORT_PLACE_ISSUED'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        $sheet->getStyle("A1:P1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('A5A5A5');
        $sheet->getStyle('A1:P1')->getFont()->setSize(8)->setBold(true)->getColor()->setARGB('000000');

        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }                

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 0;

        foreach($orders as $v){
            
            //先找房间 再找人
            foreach($v->room as $room){
                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){ 
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true); 
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";
                    } 
                    foreach($room->orderRoomCustomer as $val){   
                        $sheet->setCellValue(sprintf("A%d",$key+2),"");
                        $sheet->setCellValue(sprintf("B%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                        $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValue(sprintf("D%d",$key+2),$room_name);
                        $sheet->setCellValue(sprintf("E%d",$key+2),$room->code);
                        $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_first_name);
                        $sheet->setCellValue(sprintf("G%d",$key+2),$val->orderCustomer->en_last_name);
                        $sheet->setCellValue(sprintf("H%d",$key+2),$val->orderCustomer->first_name);
                        $sheet->setCellValue(sprintf("I%d",$key+2),$val->orderCustomer->last_name);
                        $sheet->setCellValue(sprintf("J%d",$key+2),yii::$app->params['gender_en'][$val->orderCustomer->gender]);
                        $sheet->setCellValue(sprintf("K%d",$key+2),(!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);
                        $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');
                        $sheet->setCellValue(sprintf("M%d",$key+2),$val->orderCustomer->passport);
                        $sheet->setCellValue(sprintf("N%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                        $sheet->setCellValue(sprintf("O%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                        $sheet->setCellValue(sprintf("P%d",$key+2),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : "");
                        $key++;
                    }       

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   
                                    $sheet->setCellValue(sprintf("A%d",$key+2),"");
                                    $sheet->setCellValue(sprintf("B%d",$key+2),!empty($merge_room->order->assign)?$merge_room->order->assign->name:'');
                                    $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                                    $sheet->setCellValue(sprintf("D%d",$key+2),$room_name);
                                    $sheet->setCellValue(sprintf("E%d",$key+2),$room->code);
                                    $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_first_name);
                                    $sheet->setCellValue(sprintf("G%d",$key+2),$val->orderCustomer->en_last_name);
                                    $sheet->setCellValue(sprintf("H%d",$key+2),$val->orderCustomer->first_name);
                                    $sheet->setCellValue(sprintf("I%d",$key+2),$val->orderCustomer->last_name);
                                    $sheet->setCellValue(sprintf("J%d",$key+2),yii::$app->params['gender_en'][$val->orderCustomer->gender]);
                                    $sheet->setCellValue(sprintf("K%d",$key+2),(!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);
                                    $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');
                                    $sheet->setCellValue(sprintf("M%d",$key+2),$val->orderCustomer->passport);
                                    $sheet->setCellValue(sprintf("N%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                                    $sheet->setCellValue(sprintf("O%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                                    $sheet->setCellValue(sprintf("P%d",$key+2),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : "");
                                    $key++;
                                }     

                            }
                        }


                }    
            }   

            
        }

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][6]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }


    //模板 7 领队名单
    public function Template7($product_id,$assign_id,$lead_id)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $leader = Leader::findOne($lead_id);

        $assign = OrderAssign::findOne($assign_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');

        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultRowDimension()->setRowHeight(-1);

        $sheet->getColumnDimension('A')->setWidth(4);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(8);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('L')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('M')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('N')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('O')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('P')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('Q')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(12);// 设置宽度

        $str_leader = '';
        if(!empty($leader)){
            $str_leader = sprintf("%s%s %s",$leader->first_name,$leader->last_name,$leader->phone);
        }
        $sheet->getRowDimension(1)->setRowHeight(40);
        $sheet->mergeCells('A1:S1');
        $sheet->getStyle('A1')->getAlignment()->setWrapText(true);        
       
        $sheet->getStyle('A1')->getFont()->setSize(11)->setName('微软雅黑');
        $sheet->getStyle('A1')->getFont()->setBold(true)->getColor()->setARGB('0000000');        

        $titleArray = ['','舱型','房号','姓名','姓氏(拼音)','名字(拼音)','性别','出生日期','出生地','电话','证件类型','护照号','签发地','签发日期','有效期','备注','护照','订单号','销售'];
        $sheet->fromArray($titleArray, NULL, 'A2');
        $sheet->getStyle('A2:S2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('DE1F75');
        $sheet->getStyle('A2:S2')->getFont()->setBold(true)->getColor()->setARGB('FFFFFF');

        $sheet->setCellValue("A3","领");
        if(!empty($leader)){ 
          $sheet->setCellValue("B3","")
                ->setCellValue("C3","")
                ->setCellValue("D3",$leader->first_name.$leader->last_name)
                ->setCellValue("E3",$leader->en_first_name)
                ->setCellValue("F3",$leader->en_last_name)
                ->setCellValue("G3",yii::$app->params['gender'][$leader->gender]) 
                ->setCellValue("H3",$leader->birthday)
                ->setCellValue("I3",$leader->native)
                ->setCellValue("J3",$leader->phone)
                ->setCellValue("K3","护照")
                ->setCellValue("L3",$leader->passport)
                ->setCellValue("M3",$leader->passport_add)
                ->setCellValue("N3",date('Y-m-d',strtotime($leader->passport_grant)))
                ->setCellValue("O3",date('Y-m-d',strtotime($leader->passport_date)))
                ->setCellValue("P3","")
                ->setCellValue("Q3","")
                ->setCellValue("R3","")
                ->setCellValue("S3","");
        }
             
        $key = 0;
        //大巴费 
        //获得该航线所有附加费
        $orderPt = OrderPriceType::find()->where(['product_id'=>$product_id])->andFilterWhere(['like', 'name', '大巴'])->one();
        $price_type_id = 0;
        if(!empty($orderPt)){
            $price_type_id = $orderPt->id;
        }
        //大巴人数
        $pnum = 0;
        
        foreach($assign->orders as $v){
            //获得所有附加费
            $surcharge = OrderPrice::find()->where(['order_id' => $v->id])->all();

            $orderPrice = OrderPrice::find()->where(['type'=>$price_type_id,'order_id'=>$v->id])->all();

            if(!empty($orderPrice)){
              foreach($orderPrice as $op){
                  $num = count(json_decode($op->customers,true)); 
                  $pnum += $num;
              }
            }
            if($v->product->stock == 1){ // 国内航线
                foreach($v->room as $room){
                    //if($room->stock == 1){ //计入库存 
                      if($room->stock == 1){
                        $room_name = $room->productFinalRoom->room->name.$room->productFinalRoom->room->code;
                        $room_num = $room->final_num;
                        $room_code = $room->code;
                      }elseif($room->stock == 2){
                        $merge_room = $room->mergeRoom;
                        $room_name = $merge_room->productFinalRoom->room->name.$merge_room->productFinalRoom->room->code;
                        $room_num = $merge_room->final_num;
                        $room_code = $merge_room->code;
                      }  
                      

                      $c_name = sprintf("%s %s人间",$room_name,$room_num);

                      $c_num = $v->getRoomCustomer($room->id)->count();
                      // $merge_num = 0;
                      // if($room->merge_id){ //拼房房间的人
                      //    $merge_room = $room->mergeRoom;
                      //    $merge_num = $merge_room->orderRoomCustomerNum; 
                      // }  

                      //$c_num = $c_num+$merge_num;

                      if($c_num > 0){
                        $sheet->setCellValue(sprintf("B%d",$key+4),$c_name);
                        $sheet->setCellValue(sprintf("C%d",$key+4),$room_code);
                        $sheet->mergeCells(sprintf("B%d:B%d",$key+4,$key+4+$c_num-1));
                        $sheet->mergeCells(sprintf("C%d:C%d",$key+4,$key+4+$c_num-1));
                      }
                      //$customers = $v->getRoomCustomer($room->id)->all();
                      $customers = $room->orderRoomCustomer;
                      if(!empty($customers)){
                          foreach($customers as $cust){
                            if($cust->orderCustomer->status == 1){
                               $sheet->setCellValue(sprintf("A%d",$key+4),$key+1);
                              $sheet->setCellValue(sprintf("D%d",$key+4),$cust->orderCustomer->first_name.$cust->orderCustomer->last_name);
                              $sheet->setCellValue(sprintf("E%d",$key+4),$cust->orderCustomer->en_first_name);
                              $sheet->setCellValue(sprintf("F%d",$key+4),$cust->orderCustomer->en_last_name);
                              $sheet->setCellValue(sprintf("G%d",$key+4),yii::$app->params['gender'][$cust->orderCustomer->gender]);
                              $sheet->setCellValue(sprintf("H%d",$key+4),$cust->orderCustomer->birthday);
                              $sheet->setCellValue(sprintf("I%d",$key+4),$cust->orderCustomer->native);
                              $sheet->setCellValue(sprintf("J%d",$key+4),$cust->orderCustomer->phone);
                              $sheet->setCellValue(sprintf("K%d",$key+4),"护照");
                              $sheet->setCellValue(sprintf("L%d",$key+4),$cust->orderCustomer->passport);
                              $sheet->setCellValue(sprintf("M%d",$key+4),$cust->orderCustomer->passport_add);
                              $sheet->setCellValue(sprintf("N%d",$key+4),date('Y-m-d',strtotime($cust->orderCustomer->passport_grant)));
                              $sheet->setCellValue(sprintf("O%d",$key+4),date('Y-m-d',strtotime($cust->orderCustomer->passport_date)));
                              $check = '';
                              if($cust->orderCustomer->passport_check !=2){
                                 $check = '('.yii::$app->params['passport_check'][$cust->orderCustomer->passport_check] .')';
                              }
                              $passport_type = sprintf("%s%s",yii::$app->params['passport_type'][$cust->orderCustomer->passport_type],$check);
                              //备注 附加费
                              $remark = '';
                              if(!empty($surcharge)){
                                  foreach($surcharge as $sur){
                                      $customers = json_decode($sur->customers,true);
                                      if(in_array($cust->orderCustomer->id,$customers)){
                                          $remark .= $sur->typeObj->name.',';
                                      } 
                                  }
                              }
                              $sheet->setCellValue(sprintf("P%d",$key+4),$remark);
                              $sheet->setCellValue(sprintf("Q%d",$key+4),$passport_type);
                              $sheet->setCellValueExplicit(sprintf("R%d",$key+4),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                              $sheet->setCellValue(sprintf("S%d",$key+4), isset($v->member->id) ? $v->member->profile->name : '(未设置)' );
                              $key++;
                            }  
                          }  
                      }
                      // if($room->merge_id){ //拼房房间的人
                      //   $merge_room = $room->mergeRoom;
                      //   $surcharge_merge = $surcharge = OrderPrice::find()->where(['order_id' => $merge_room->order->id])->all();
                      //   $customers_merge = $merge_room->orderRoomCustomer;
                      //   if($customers_merge){
                      //       foreach($customers_merge as $cust){   
                      //            if($cust->orderCustomer->status == 1){
                      //              $sheet->setCellValue(sprintf("A%d",$key+4),$key+1);
                      //             $sheet->setCellValue(sprintf("D%d",$key+4),$cust->orderCustomer->first_name.$cust->orderCustomer->last_name);
                      //             $sheet->setCellValue(sprintf("E%d",$key+4),$cust->orderCustomer->en_first_name.'/'.$cust->orderCustomer->en_last_name);
                      //             $sheet->setCellValue(sprintf("F%d",$key+4),yii::$app->params['gender'][$cust->orderCustomer->gender]);
                      //             $sheet->setCellValue(sprintf("G%d",$key+4),$cust->orderCustomer->birthday);
                      //             $sheet->setCellValue(sprintf("H%d",$key+4),$cust->orderCustomer->native);
                      //             $sheet->setCellValue(sprintf("I%d",$key+4),$cust->orderCustomer->phone);
                      //             $sheet->setCellValue(sprintf("J%d",$key+4),"护照");
                      //             $sheet->setCellValue(sprintf("K%d",$key+4),$cust->orderCustomer->passport);
                      //             $sheet->setCellValue(sprintf("L%d",$key+4),$cust->orderCustomer->passport_add);
                      //             $sheet->setCellValue(sprintf("M%d",$key+4),date('Y-m-d',strtotime($cust->orderCustomer->passport_grant)));
                      //             $check = '';
                      //             if($cust->orderCustomer->passport_check !=2){
                      //                $check = '('.yii::$app->params['passport_check'][$cust->orderCustomer->passport_check] .')';
                      //             }
                      //             $passport_type = sprintf("%s%s",yii::$app->params['passport_type'][$cust->orderCustomer->passport_type],$check);
                      //             //备注 附加费
                      //             $remark = '';
                      //             if(!empty($surcharge_merge)){
                      //                 foreach($surcharge_merge as $sur){
                      //                     $customers = json_decode($sur->customers,true);
                      //                     if(in_array($cust->orderCustomer->id,$customers)){
                      //                         $remark .= $sur->typeObj->name.',';
                      //                     } 
                      //                 }
                      //             }
                      //             $sheet->setCellValue(sprintf("N%d",$key+4),$remark);
                      //             $sheet->setCellValue(sprintf("O%d",$key+4),$passport_type);
                      //             $sheet->setCellValueExplicit(sprintf("P%d",$key+4),$merge_room->order->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                      //             $key++;
                      //           }  
                      //       }     

                      //    }
                      // }
                      

                    //}

                    
                    

               }
            }elseif($v->product->stock == 2){ // 海外航线
                  $t = json_decode($v->product->rooms, true); 
                  foreach($v->room as $room){ // orderRoom
                  //if($room->stock == 1){ //计入库存 
                    if($room->stock == 1){
                        $room_name = $t[$room->final_room_id]['name'].' '.$room->final_num.'人';
                        $room_code = $room->code;
                    }elseif($room->stock ==2){
                        $merge_room = $room->mergeRoom;
                        $room_name = $t[$merge_room->final_room_id]['name'].' '.$merge_room->final_num.'人';
                        $room_code = $merge_room->code;
                    }
                   
                    $c_num = $v->getRoomCustomer($room->id)->count();
                    // $merge_num = 0;
                    // if($room->merge_id){ //拼房房间的人
                    //     $merge_room = $room->mergeRoom;
                    //     $merge_num = $merge_room->orderRoomCustomerNum; 
                    // }  
                    // $c_num = $c_num+$merge_num;
                    if($c_num > 0){
                      $sheet->setCellValue(sprintf("B%d",$key+4),$room_name);
                      $sheet->setCellValue(sprintf("C%d",$key+4),$room_code);
                      $sheet->mergeCells(sprintf("B%d:B%d",$key+4,$key+4+$c_num-1));
                      $sheet->mergeCells(sprintf("C%d:C%d",$key+4,$key+4+$c_num-1));
                    }

                    //$customers = $v->getRoomCustomer($room->id)->all();
                    $customers = $room->orderRoomCustomer;
                    if(!empty($customers)){
                        foreach($customers as $cust){
                        if($cust->orderCustomer->status == 1){
                          $sheet->setCellValue(sprintf("A%d",$key+4),$key+1);
                          $sheet->setCellValue(sprintf("D%d",$key+4),$cust->orderCustomer->first_name.$cust->orderCustomer->last_name);
                          $sheet->setCellValue(sprintf("E%d",$key+4),$cust->orderCustomer->en_first_name);
                          $sheet->setCellValue(sprintf("F%d",$key+4),$cust->orderCustomer->en_last_name);
                          $sheet->setCellValue(sprintf("G%d",$key+4),yii::$app->params['gender'][$cust->orderCustomer->gender]);
                          $sheet->setCellValue(sprintf("H%d",$key+4),$cust->orderCustomer->birthday);
                          $sheet->setCellValue(sprintf("I%d",$key+4),$cust->orderCustomer->native);
                          $sheet->setCellValue(sprintf("J%d",$key+4),$cust->orderCustomer->phone);
                          $sheet->setCellValue(sprintf("K%d",$key+4),"护照");
                          $sheet->setCellValue(sprintf("L%d",$key+4),$cust->orderCustomer->passport);
                          $sheet->setCellValue(sprintf("M%d",$key+4),$cust->orderCustomer->passport_add);
                          $sheet->setCellValue(sprintf("N%d",$key+4),date('Y-m-d',strtotime($cust->orderCustomer->passport_grant)));
                          $sheet->setCellValue(sprintf("O%d",$key+4),date('Y-m-d',strtotime($cust->orderCustomer->passport_date)));
                          $check = '';
                          if($cust->orderCustomer->passport_check !=2){
                             $check = '('.yii::$app->params['passport_check'][$cust->orderCustomer->passport_check] .')';
                          }
                          $passport_type = sprintf("%s%s",yii::$app->params['passport_type'][$cust->orderCustomer->passport_type],$check);
                          //备注 附加费
                          $remark = '';
                          if(!empty($surcharge)){
                              foreach($surcharge as $sur){
                                  $customers = json_decode($sur->customers,true);
                                  if(in_array($cust->orderCustomer->id,$customers)){
                                      $remark .= $sur->typeObj->name.',';
                                  } 
                              }
                          }
                          $sheet->setCellValue(sprintf("P%d",$key+4),$remark);
                          $sheet->setCellValue(sprintf("Q%d",$key+4),$passport_type);
                          $sheet->setCellValueExplicit(sprintf("R%d",$key+4),$v->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                          $sheet->setCellValue(sprintf("S%d",$key+4), isset($v->member->id) ? $v->member->profile->name : '(未设置)' );
                          $key++;
                         } 
                        }  
                    }
                    // if($room->merge_id){ //拼房房间的人
                    //     $merge_room = $room->mergeRoom;
                    //     $surcharge_merge = $surcharge = OrderPrice::find()->where(['order_id' => $merge_room->order->id])->all();
                    //     $customers_merge = $merge_room->orderRoomCustomer;
                    //     if($customers_merge){
                    //         foreach($customers_merge as $cust){   
                    //              if($cust->orderCustomer->status == 1){
                    //                $sheet->setCellValue(sprintf("A%d",$key+4),$key+1);
                    //               $sheet->setCellValue(sprintf("D%d",$key+4),$cust->orderCustomer->first_name.$cust->orderCustomer->last_name.'*');
                    //               $sheet->setCellValue(sprintf("E%d",$key+4),$cust->orderCustomer->en_first_name.'/'.$cust->orderCustomer->en_last_name);
                    //               $sheet->setCellValue(sprintf("F%d",$key+4),yii::$app->params['gender'][$cust->orderCustomer->gender]);
                    //               $sheet->setCellValue(sprintf("G%d",$key+4),$cust->orderCustomer->birthday);
                    //               $sheet->setCellValue(sprintf("H%d",$key+4),$cust->orderCustomer->native);
                    //               $sheet->setCellValue(sprintf("I%d",$key+4),$cust->orderCustomer->phone);
                    //               $sheet->setCellValue(sprintf("J%d",$key+4),"护照");
                    //               $sheet->setCellValue(sprintf("K%d",$key+4),$cust->orderCustomer->passport);
                    //               $sheet->setCellValue(sprintf("L%d",$key+4),$cust->orderCustomer->passport_add);
                    //               $sheet->setCellValue(sprintf("M%d",$key+4),date('Y-m-d',strtotime($cust->orderCustomer->passport_grant)));
                    //               $check = '';
                    //               if($cust->orderCustomer->passport_check !=2){
                    //                  $check = '('.yii::$app->params['passport_check'][$cust->orderCustomer->passport_check] .')';
                    //               }
                    //               $passport_type = sprintf("%s%s",yii::$app->params['passport_type'][$cust->orderCustomer->passport_type],$check);
                    //               //备注 附加费
                    //               $remark = '';
                    //               if(!empty($surcharge_merge)){
                    //                   foreach($surcharge_merge as $sur){
                    //                       $customers = json_decode($sur->customers,true);
                    //                       if(in_array($cust->orderCustomer->id,$customers)){
                    //                           $remark .= $sur->typeObj->name.',';
                    //                       } 
                    //                   }
                    //               }
                    //               $sheet->setCellValue(sprintf("N%d",$key+4),$remark);
                    //               $sheet->setCellValue(sprintf("O%d",$key+4),$passport_type);
                    //               $sheet->setCellValueExplicit(sprintf("P%d",$key+4),$merge_room->order->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                    //               $key++;
                    //             }  
                    //         }     

                    //      }
                    // }

                  //}
                }       
            } 
             
        }

        $sheet->getStyle(sprintf("P1:P%d",$key+4))->getAlignment()->setWrapText(true);
        //获取人员数量
        $people_num = 0;
        foreach($assign->orders as $v){
            foreach($v->room as $room){
                //if($room->stock == 1){
                    $people_num += $room->orderRoomCustomerNum;
                    // if($room->merge_id){ //拼房房间的人
                    //     $merge_room = $room->mergeRoom;
                    //     $people_num += $merge_room->orderRoomCustomerNum;
                    // }
                //}

            }
            
        }

        $sheet->setCellValue("A1",sprintf("%s %s-%s\n%s：%s 【团队人数%s+1人  %s大巴%s+1人】",$product->name,date("Y年m月d日",strtotime($product->go_time)),date("Y年m月d日",strtotime($product->return_time)),$assign->name,$str_leader,$people_num,yii::$app->params['city'][$product->from_city],$pnum));

        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle(sprintf('A2:S%s',$key+3))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $leader_name = '';

        if(!empty($leader)){
           $leader_name = sprintf("%s%s",$leader->first_name,$leader->last_name);
        }
        
        $down_name = sprintf("%s_%s_%s_%s_%s",$assign->name,$leader_name,$product->name,$product->go_time,yii::$app->params['template'][7]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'assign_name' => $assign->name,
                'leader_name' => $leader_name,
                'down_name' => $down_name,
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 8 处女星号名单
    // public function Template8($product_id,$type)
    // {
    //     if(empty($product_id)){
    //         return false;
    //     }
    //     $product = Product::findOne($product_id);

    //     $objPHPExcel = new \PHPExcel();
    //     $sheet = $objPHPExcel->getActiveSheet(0);
    //     $sheet->setTitle('团队名单');
    //     $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
    //     $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //     $sheet->getDefaultColumnDimension()->setWidth(10);
    //     $styleArray = [
    //          'borders' => [
    //              'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
    //          ],
    //     ]; //设置border
    //     $sheet->getStyle('A1:S1')->applyFromArray($styleArray);
    //     $sheet->getStyle('A1:S1')->getAlignment()->setWrapText(true);
    //     $sheet->getColumnDimension('A')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('D')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('E')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('F')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('G')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('H')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('I')->setWidth(10);// 设置宽度
    //     $sheet->getColumnDimension('J')->setWidth(10);// 设置宽度 
    //     $sheet->getColumnDimension('K')->setWidth(10);// 设置宽度 
    //     $sheet->getColumnDimension('L')->setWidth(12);// 设置宽度 
    //     $sheet->getColumnDimension('O')->setWidth(20);// 设置宽度 
    //     $sheet->getRowDimension(1)->setRowHeight(30);       

                                     
    //     $titleArray = ['团号','房间类型','房间序号','客人姓名','姓（拼音）','名（拼音）','性别','出生日期','出生地','护照号','护照签发地','护照签发日期','护照有效期','手机号','身份证号','国籍','备注','户籍住址','职业现状（所在公司或单位名称需要填写清楚）'];
    //     $sheet->fromArray($titleArray, NULL, 'A1');

    //     $sheet->getStyle("A1:S1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
    //     $sheet->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('D8D8D8');
    //     $sheet->getStyle('A1:P1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');

    //     if($type == 3){ //全部订单
    //         $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
    //     }else if($type == 2){ //已付款订单
    //         $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
    //     }else if($type == 1){ //已审核订单
    //         $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
    //     }else if($type == 4){ //已提交订单
    //         $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
    //     }                

    //     //省份中英文
    //     $province = yii::$app->params['area']['province'];
    //     $province = array_flip($province);
    //     $province = array_merge($province,yii::$app->params['area']['country_english']);
    //     $country_code_2 = yii::$app->params['area']['country_code_2'];

    //     $key = 0;

    //     foreach($orders as $v){
            
    //         //先找房间 再找人
    //         foreach($v->room as $room){
    //             if($room->stock == 1){ //计入库存
    //                 if($v->product->stock == 1){ 
    //                     $productRoom = ProductRoom::findOne($room->final_room_id);
    //                     //房间名称
    //                     $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
    //                     //房间类型
    //                     $room_type = $productRoom->room->code;
    //                     //房间号
    //                     $room_code = $room->code;
    //                     //最大入住人数
    //                     $room_num = $productRoom->room->num;
    //                 }elseif($v->product->stock == 2){
    //                     $t = json_decode($v->product->rooms, true); 
    //                     $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
    //                     $room_code = $room->code;
    //                     //最大入住人数 为空
    //                     $room_num = "";

    //                     $room_type = "";
    //                 } 
                    
    //                 foreach($room->orderRoomCustomer as $val){   
    //                     $sheet->setCellValue(sprintf("A%d",$key+2),!empty($v->assign)?$v->assign->name:'');
    //                     $sheet->setCellValue(sprintf("B%d",$key+2),$room_type);
    //                     $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);
    //                     $sheet->setCellValue(sprintf("D%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
    //                     $sheet->setCellValue(sprintf("E%d",$key+2),$val->orderCustomer->en_first_name);
    //                     $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_last_name);
    //                     $sheet->setCellValue(sprintf("G%d",$key+2),yii::$app->params['gender'][$val->orderCustomer->gender]);
    //                     $sheet->setCellValue(sprintf("H%d",$key+2),!empty($val->orderCustomer->birthday) ? date("Y-m-d",strtotime($val->orderCustomer->birthday)) :'');
    //                     $sheet->setCellValue(sprintf("I%d",$key+2),$val->orderCustomer->native);
    //                     $sheet->setCellValue(sprintf("J%d",$key+2),$val->orderCustomer->passport);
    //                     $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport_add);
    //                     $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("Y-m-d",strtotime($val->orderCustomer->passport_grant)) :'');
    //                     $sheet->setCellValue(sprintf("M%d",$key+2), !empty($val->orderCustomer->passport_date) ? date("Y-m-d",strtotime($val->orderCustomer->passport_date)) :'');
    //                     $sheet->setCellValueExplicit(sprintf("N%d",$key+2),$val->orderCustomer->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
    //                     $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);
    //                     $sheet->setCellValue(sprintf("P%d",$key+2), (!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);
    //                     $sheet->setCellValue(sprintf("R%d",$key+2),"");
    //                     $sheet->setCellValue(sprintf("S%d",$key+2),"");
                       
    //                     $key++;
    //                 }       

    //                 if($room->merge_id){ //拼房房间的人
    //                     $merge_room = $room->mergeRoom;
    //                     $customers = $merge_room->orderRoomCustomer;
    //                         if($customers){
    //                             foreach($customers as $val){   
    //                                 $sheet->setCellValue(sprintf("A%d",$key+2),!empty($v->assign)?$v->assign->name:'');
    //                                 $sheet->setCellValue(sprintf("B%d",$key+2),$room_type);
    //                                 $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);
    //                                 $sheet->setCellValue(sprintf("D%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
    //                                 $sheet->setCellValue(sprintf("E%d",$key+2),$val->orderCustomer->en_first_name);
    //                                 $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_last_name);
    //                                 $sheet->setCellValue(sprintf("G%d",$key+2),yii::$app->params['gender'][$val->orderCustomer->gender]);
    //                                 $sheet->setCellValue(sprintf("H%d",$key+2),!empty($val->orderCustomer->birthday) ? date("Y-m-d",strtotime($val->orderCustomer->birthday)) :'');
    //                                 $sheet->setCellValue(sprintf("I%d",$key+2),$val->orderCustomer->native);
    //                                 $sheet->setCellValue(sprintf("J%d",$key+2),$val->orderCustomer->passport);
    //                                 $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport_add);
    //                                 $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("Y-m-d",strtotime($val->orderCustomer->passport_grant)) :'');
    //                                 $sheet->setCellValue(sprintf("M%d",$key+2), !empty($val->orderCustomer->passport_date) ? date("Y-m-d",strtotime($val->orderCustomer->passport_date)) :'');
    //                                 $sheet->setCellValueExplicit(sprintf("N%d",$key+2),$val->orderCustomer->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
    //                                 $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);
    //                                 $sheet->setCellValue(sprintf("P%d",$key+2), (!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);
    //                                 $sheet->setCellValue(sprintf("R%d",$key+2),"");
    //                                 $sheet->setCellValue(sprintf("S%d",$key+2),"");
    //                                 $key++;
    //                             }     

    //                         }
    //                     }


    //             }    
    //         }   

            
    //     }

    //     $sheet->getStyle(sprintf("A2:S%d",$key+1))->applyFromArray($styleArray);

    //     $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    //     $fileName = sprintf("%s.xlsx",uniqid());
    //     $path = 'uploads/excel/'.date('Ym').'/';
    //     if(!is_dir($path)){  
    //           mkdir($path,0777,true);  
    //     }
    //     $file = $path.$fileName;
    //     $objWriter->save($file);
        
    //     $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][8]);
    //     $down_name = str_replace("\"","",$down_name); //删除双引号
    //     $down_name = str_replace("'","",$down_name);  //删除单引号
    //     $down_name = preg_replace("/\s/","",$down_name); //去除空格
    //     $res = [ 
    //             'down_name' => $down_name.".xlsx",
    //             'down_url'  => $file,
    //           ];  
    //     return $res;
    // }
    //模板 8 处女星号名单
    public function Template8($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Calibri');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $sheet->getRowDimension(1)->setRowHeight(40); 
        $sheet->getRowDimension(2)->setRowHeight(40);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border

        $titleArray1 = ["Booking Source","Initial Date" , "Agency ID" , 
          "Currency Code" , "Market Code" , "Office Code" , 
          "Agr ID" , "Agent Reference" , "Agent Contact" , 
          "Advert Code" , "Pax Type" , "Cost Center", 
          "Cruise ID" , "Category Code" , "Cabin Number" , 
          "Price ID/ Promo Code" , "Greeting" ,"Gender", "Firstname" ,
          "Lastname" , "Localized First Name" , "Localized Last Name" , "Date of Birth" , "Nationality" , "Residency", "Client Language",
           "Passport No." , "Passport Issued At" , "Passport Issue Date" , "Passport Expiry Date" ,"National ID","National ID Type","National ID Issued Country",
           "National ID Issued Date","National ID Expire Date", "National ID (2)","National ID Type (2)","National ID Issued Country (2)","National ID Issued Date (2)","National ID Expire Date (2)","Visa Country","Visa Type",
           "Visa Number","Visa Issued Date","Visa Expire Date",
           "Passenger Email Address","Tel Country Code","Phone Number","Emergency Contact Name",  "Emergency Contact Email" ,"Emergency Tel Country Code",
          "Emergency Contact No." , "Paid" , "Amount" ,
          "Form of Payment" , "Cash Description/ Check Number" , 
          "Received From" , "Comments" , "SourceID" , "Address" , "City" , "Country Code" ,"State/Province" , "District" , "Zip Code" ,"AUTO BC" , "AUTO CS" , "SHOREX" ,"ADDON" , "CBS RETAIL PROGRAM" , "AMOUNT CBS" ,
          "ClubAccNumber" , "TierLevel" , "Group ID" ,
          "ReserveType" , "Alt Res ID"
        ];

        $titleArray2 = [
            '','','','','','','','团号', '','','','','航 次（出发日期）','房 间 类 别 ','房 间 序 號',' ', '性 别','性 别','名 字（英 文/拼音）', '姓 氏（英 文/拼音）','名字（中文）', '姓 氏（中文）', '出 生 日 期', '国 籍','','','护 照 号 码','','','护 照 到 期 日', '往 來 港 澳 通 行 证号 码（中国居民） / 身份证号码（香港、澳门居民）','证件类型', '','', '证件有效日期','证件号码（选填）','证件类型 （选填）', '','','证件有效日期（选填）', '','','','','','','','','','','','','','','','','','','','','','','','','','','','岸 上 观 光 团 ','落地签/穿 梭 巴 士 编 码（前 往 登 船）/穿 梭 巴 士 编 码 （回 程 离 开）','','','','','团 号','',''
        ];    

        $sheet->fromArray($titleArray1, NULL, 'A1');
        $sheet->fromArray($titleArray2, NULL, 'A2');

      

        $sheet->getStyle("A1:BX2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_BOTTOM);

        $sheet->getStyle('A1:P2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('31859C');
        $sheet->getStyle('Q1:AZ2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('953735');
        $sheet->getStyle('BA1:BG2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('007E39');
        $sheet->getStyle('BH1:BO2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('595959');
        $sheet->getStyle('BP1:BX2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('31859C');
        
        $sheet->getStyle('A1:BX1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');
        $sheet->getStyle('A2:BX2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
      
        $sheet->getStyle('A1:BX2')->getAlignment()->setWrapText(true);

        $sheet->getStyle('A1:BX2')->applyFromArray($styleArray);


        $sheet->getColumnDimension('H')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('L')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('M')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('N')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('O')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('Q')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('T')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('U')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('V')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('W')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('AA')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('AD')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('BV')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('X')->setWidth(20);// 设置宽度

        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }                

        $m_gender = [
            1 => 'Mr',
            2 => 'Ms',
        ];

        $e_gender = [
            1 => 'M',
            2 => 'F',
        ];

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 1;

        foreach($orders as $v){
            
            //先找房间 再找人
            foreach($v->room as $room){
                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){ 
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间类型
                        $room_type = $productRoom->room->code;
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true); 
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";

                        $room_type = "";
                    } 
                    
                    foreach($room->orderRoomCustomer as $val){   
                        $sheet->setCellValue(sprintf("H%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                        $sheet->setCellValue(sprintf("M%d",$key+2),!empty($product->go_time)? 'SV'.date("ymd",strtotime($product->go_time)):'');

                        $sheet->setCellValue(sprintf("N%d",$key+2),$room_type);

                        $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);

                        $sheet->setCellValue(sprintf("Q%d",$key+2),$m_gender[$val->orderCustomer->gender]);

                        $sheet->setCellValue(sprintf("R%d",$key+2),$e_gender[$val->orderCustomer->gender]);

                        $sheet->setCellValue(sprintf("S%d",$key+2),$val->orderCustomer->en_last_name);
                        $sheet->setCellValue(sprintf("T%d",$key+2),$val->orderCustomer->en_first_name);

                        $sheet->setCellValue(sprintf("U%d",$key+2),$val->orderCustomer->last_name);
                        
                        $sheet->setCellValue(sprintf("V%d",$key+2),$val->orderCustomer->first_name);
                        
                       
                        $sheet->setCellValue(sprintf("W%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');

                        $sheet->setCellValue(sprintf("X%d",$key+2), (!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);

                        $sheet->setCellValue(sprintf("AA%d",$key+2),$val->orderCustomer->passport);

                        
                       
                        $sheet->setCellValue(sprintf("AD%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                       
                        $sheet->setCellValue(sprintf("BV%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                        
                        $key++;
                    }       

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   
                                     $sheet->setCellValue(sprintf("H%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                                    $sheet->setCellValue(sprintf("M%d",$key+2),!empty($product->go_time)? 'SV'.date("ymd",strtotime($product->go_time)):'');

                                    $sheet->setCellValue(sprintf("N%d",$key+2),$room_type);

                                    $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);

                                    $sheet->setCellValue(sprintf("Q%d",$key+2),$m_gender[$val->orderCustomer->gender]);
                                    $sheet->setCellValue(sprintf("R%d",$key+2),$e_gender[$val->orderCustomer->gender]);

                                    $sheet->setCellValue(sprintf("S%d",$key+2),$val->orderCustomer->en_last_name);
                                    $sheet->setCellValue(sprintf("T%d",$key+2),$val->orderCustomer->en_first_name);

                                    $sheet->setCellValue(sprintf("U%d",$key+2),$val->orderCustomer->last_name);
                                    $sheet->setCellValue(sprintf("V%d",$key+2),$val->orderCustomer->first_name);
                                   
                                   
                                    $sheet->setCellValue(sprintf("W%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');

                                    $sheet->setCellValue(sprintf("X%d",$key+2), (!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);

                                    $sheet->setCellValue(sprintf("AA%d",$key+2),$val->orderCustomer->passport);

                                    $sheet->setCellValue(sprintf("AD%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                                   
                                    $sheet->setCellValue(sprintf("BV%d",$key+2),!empty($v->assign)?$v->assign->name:'');

                            
                                    $key++;
                                }     

                            }
                        }


                }    
            }   

            
        }

        $sheet->getStyle(sprintf("A3:BX%d",$key+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][8]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 9 星梦名单
    public function Template9($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Calibri');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $sheet->getRowDimension(1)->setRowHeight(40); 
        $sheet->getRowDimension(2)->setRowHeight(40);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border

        $titleArray1 = ["Booking Source","Initial Date" , "Agency ID" , 
          "Currency Code" , "Market Code" , "Office Code" , 
          "Agr ID" , "Agent Reference" , "Agent Contact" , 
          "Advert Code" , "Pax Type" , "Cost Center", 
          "Cruise ID" , "Category Code" , "Cabin Number" , 
          "Price ID/ Promo Code" , "Greeting" ,"Gender", "Firstname" ,
          "Lastname" , "Localized First Name" , "Localized Last Name" , "Date of Birth" , "Nationality" , "Residency", "Client Language",
           "Passport No." , "Passport Issued At" , "Passport Issue Date" , "Passport Expiry Date" ,"National ID","National ID Type","National ID Issued Country",
           "National ID Issued Date","National ID Expire Date", "National ID (2)","National ID Type (2)","National ID Issued Country (2)","National ID Issued Date (2)","National ID Expire Date (2)","Visa Country","Visa Type",
           "Visa Number","Visa Issued Date","Visa Expire Date",
           "Passenger Email Address","Tel Country Code","Phone Number","Emergency Contact Name",  "Emergency Contact Email" ,"Emergency Tel Country Code",
          "Emergency Contact No." , "Paid" , "Amount" ,
          "Form of Payment" , "Cash Description/ Check Number" , 
          "Received From" , "Comments" , "SourceID" , "Address" , "City" , "Country Code" ,"State/Province" , "District" , "Zip Code" ,"AUTO BC" , "AUTO CS" , "SHOREX" ,"ADDON" , "CBS RETAIL PROGRAM" , "AMOUNT CBS" ,
          "ClubAccNumber" , "TierLevel" , "Group ID" ,
          "ReserveType" , "Alt Res ID"
        ];

        $titleArray2 = [
            '','','','','','','','团号', '','','','','航 次（出发日期）','房 间 类 别 ','房 间 序 號',' ', '性 别','性 别','名 字（英 文/拼音）', '姓 氏（英 文/拼音）','名字（中文）', '姓 氏（中文）', '出 生 日 期', '国 籍','','','护 照 号 码','','','护 照 到 期 日', '往 來 港 澳 通 行 证号 码（中国居民） / 身份证号码（香港、澳门居民）','证件类型', '','', '证件有效日期','证件号码（选填）','证件类型 （选填）', '','','证件有效日期（选填）', '','','','','','','','','','','','','','','','','','','','','','','','','','','','岸 上 观 光 团 ','落地签/穿 梭 巴 士 编 码（前 往 登 船）/穿 梭 巴 士 编 码 （回 程 离 开）','','','','','团 号','',''
        ];    

        $sheet->fromArray($titleArray1, NULL, 'A1');
        $sheet->fromArray($titleArray2, NULL, 'A2');

      

        $sheet->getStyle("A1:BX2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_BOTTOM);

        $sheet->getStyle('A1:P2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('31859C');
        $sheet->getStyle('Q1:AZ2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('953735');
        $sheet->getStyle('BA1:BG2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('007E39');
        $sheet->getStyle('BH1:BO2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('595959');
        $sheet->getStyle('BP1:BX2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('31859C');
        
        $sheet->getStyle('A1:BX1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');
        $sheet->getStyle('A2:BX2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
      
        $sheet->getStyle('A1:BX2')->getAlignment()->setWrapText(true);

        $sheet->getStyle('A1:BX2')->applyFromArray($styleArray);


        $sheet->getColumnDimension('H')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('L')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('M')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('N')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('O')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('Q')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('T')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('U')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('V')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('W')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('AA')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('AD')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('BV')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('X')->setWidth(20);// 设置宽度

        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }                

        $m_gender = [
            1 => 'Mr',
            2 => 'Ms',
        ];

        $e_gender = [
            1 => 'M',
            2 => 'F',
        ];

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 1;

        foreach($orders as $v){
            
            //先找房间 再找人
            foreach($v->room as $room){
                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){ 
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间类型
                        $room_type = $productRoom->room->code;
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true); 
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";

                        $room_type = "";
                    } 
                    
                    foreach($room->orderRoomCustomer as $val){   
                        $sheet->setCellValue(sprintf("H%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                        $sheet->setCellValue(sprintf("M%d",$key+2),!empty($product->go_time)? 'SV'.date("ymd",strtotime($product->go_time)):'');

                        $sheet->setCellValue(sprintf("N%d",$key+2),$room_type);

                        $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);

                        $sheet->setCellValue(sprintf("Q%d",$key+2),$m_gender[$val->orderCustomer->gender]);

                        $sheet->setCellValue(sprintf("R%d",$key+2),$e_gender[$val->orderCustomer->gender]);

                        $sheet->setCellValue(sprintf("S%d",$key+2),$val->orderCustomer->en_last_name);
                        $sheet->setCellValue(sprintf("T%d",$key+2),$val->orderCustomer->en_first_name);

                        $sheet->setCellValue(sprintf("U%d",$key+2),$val->orderCustomer->last_name);
                        
                        $sheet->setCellValue(sprintf("V%d",$key+2),$val->orderCustomer->first_name);
                        
                       
                        $sheet->setCellValue(sprintf("W%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');

                        $sheet->setCellValue(sprintf("X%d",$key+2), (!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);

                        $sheet->setCellValue(sprintf("AA%d",$key+2),$val->orderCustomer->passport);

                        
                       
                        $sheet->setCellValue(sprintf("AD%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                       
                        $sheet->setCellValue(sprintf("BV%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                        
                        $key++;
                    }       

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   
                                     $sheet->setCellValue(sprintf("H%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                                    $sheet->setCellValue(sprintf("M%d",$key+2),!empty($product->go_time)? 'SV'.date("ymd",strtotime($product->go_time)):'');

                                    $sheet->setCellValue(sprintf("N%d",$key+2),$room_type);

                                    $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);

                                    $sheet->setCellValue(sprintf("Q%d",$key+2),$m_gender[$val->orderCustomer->gender]);
                                    $sheet->setCellValue(sprintf("R%d",$key+2),$e_gender[$val->orderCustomer->gender]);

                                    $sheet->setCellValue(sprintf("S%d",$key+2),$val->orderCustomer->en_last_name);
                                    $sheet->setCellValue(sprintf("T%d",$key+2),$val->orderCustomer->en_first_name);

                                    $sheet->setCellValue(sprintf("U%d",$key+2),$val->orderCustomer->last_name);
                                    $sheet->setCellValue(sprintf("V%d",$key+2),$val->orderCustomer->first_name);
                                   
                                   
                                    $sheet->setCellValue(sprintf("W%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');

                                    $sheet->setCellValue(sprintf("X%d",$key+2), (!empty($country_code_2[$val->orderCustomer->country]))?$country_code_2[$val->orderCustomer->country]:$val->orderCustomer->country);

                                    $sheet->setCellValue(sprintf("AA%d",$key+2),$val->orderCustomer->passport);

                                    $sheet->setCellValue(sprintf("AD%d",$key+2),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                                   
                                    $sheet->setCellValue(sprintf("BV%d",$key+2),!empty($v->assign)?$v->assign->name:'');

                            
                                    $key++;
                                }     

                            }
                        }


                }    
            }   

            
        }

        $sheet->getStyle(sprintf("A3:BX%d",$key+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][9]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 10 携程模板
    public function Template10($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:P1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:P1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(14);// 设置宽度 
        $sheet->getColumnDimension('K')->setWidth(14);// 设置宽度 
        $sheet->getColumnDimension('L')->setWidth(20);// 设置宽度 
        $sheet->getColumnDimension('O')->setWidth(14);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(30);       

                                     
        $titleArray = ['航次','序号','团号','房型','中文名','英文名','生日','性别','国籍','联系电话','护照号','身份证号','出生地','签发地','签发日期','有效期'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        $sheet->getStyle("A1:P1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('F79646');
        $sheet->getStyle('A1:P1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');

        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }                

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 0;

        foreach($orders as $v){
            
            //先找房间 再找人
            foreach($v->room as $room){
                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){ 
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间类型
                        $room_type = $productRoom->room->code;
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true); 
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";

                        $room_type = "";
                    } 

                    $sheet->setCellValue(sprintf("D%d",$key+2),$room_name);

                    foreach($room->orderRoomCustomer as $val){   
                         $sheet->setCellValue(sprintf("A%d",$key+2),!empty($product->code)?$product->code:'');
                        $sheet->setCellValue(sprintf("B%d",$key+2),$key+1);

                        $sheet->setCellValue(sprintf("C%d",$key+2),!empty($v->assign)?$v->assign->name:'');

                        $sheet->setCellValue(sprintf("E%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);

                        $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_first_name.'/'.$val->orderCustomer->en_last_name);

                        $sheet->setCellValue(sprintf("G%d",$key+2),!empty($val->orderCustomer->birthday) ? date("Y/m/d",strtotime($val->orderCustomer->birthday)) :'');

                        $sheet->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender'][$val->orderCustomer->gender]);
                        
                        $sheet->setCellValue(sprintf("I%d",$key+2),$val->orderCustomer->country);
                      

                        $sheet->setCellValueExplicit(sprintf("J%d",$key+2),$val->orderCustomer->phone,\PHPExcel_Cell_DataType::TYPE_STRING);

                        $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport);

                        $sheet->setCellValueExplicit(sprintf("L%d",$key+2),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);

                        $sheet->setCellValue(sprintf("M%d",$key+2),$val->orderCustomer->native);

                        $sheet->setCellValue(sprintf("N%d",$key+2),$val->orderCustomer->passport_add);

                       
                        $sheet->setCellValue(sprintf("O%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("Y/m/d",strtotime($val->orderCustomer->passport_grant)) :'');
                        $sheet->setCellValue(sprintf("P%d",$key+2), !empty($val->orderCustomer->passport_date) ? date("Y/m/d",strtotime($val->orderCustomer->passport_date)) :'');
                                             
                        $key++;
                    }       

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   

                                  $sheet->setCellValue(sprintf("A%d",$key+2),!empty($product->code)?$product->code:'');
                                  $sheet->setCellValue(sprintf("B%d",$key+2),$key+1);

                                  $sheet->setCellValue(sprintf("C%d",$key+2),!empty($v->assign)?$v->assign->name:'');
                                
                                  $sheet->setCellValue(sprintf("E%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);

                                  $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_first_name.'/'.$val->orderCustomer->en_last_name);

                                  $sheet->setCellValue(sprintf("G%d",$key+2),!empty($val->orderCustomer->birthday) ? date("Y/m/d",strtotime($val->orderCustomer->birthday)) :'');

                                  $sheet->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender'][$val->orderCustomer->gender]);
                                  
                                  $sheet->setCellValue(sprintf("I%d",$key+2),$val->orderCustomer->country);
                                

                                  $sheet->setCellValueExplicit(sprintf("J%d",$key+2),$val->orderCustomer->phone,\PHPExcel_Cell_DataType::TYPE_STRING);

                                  $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport);

                                  $sheet->setCellValueExplicit(sprintf("L%d",$key+2),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);

                                  $sheet->setCellValue(sprintf("M%d",$key+2),$val->orderCustomer->native);

                                  $sheet->setCellValue(sprintf("N%d",$key+2),$val->orderCustomer->passport_add);

                                  $sheet->setCellValue(sprintf("O%d",$key+2),!empty($val->orderCustomer->passport_grant) ? date("Y/m/d",strtotime($val->orderCustomer->passport_grant)) :'');
                                  $sheet->setCellValue(sprintf("P%d",$key+2), !empty($val->orderCustomer->passport_date) ? date("Y/m/d",strtotime($val->orderCustomer->passport_date)) :'');
                                                       
                                  $key++;
                                }     

                            }
                        }


                }    
            }   

            
        }

        $sheet->getStyle(sprintf("A2:P%d",$key+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][10]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 11 北京巴比来
    public function Template11($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:M1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:M1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(6);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(10);// 设置宽度 
        $sheet->getColumnDimension('K')->setWidth(16);// 设置宽度 
        $sheet->getColumnDimension('L')->setWidth(14);// 设置宽度 
        $sheet->getColumnDimension('M')->setWidth(20);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(50);       

                                     
        $titleArray = ['No.','Bkg ID','旅行社代码 TA Code','游客代码 Pax ID','拼音姓 Last Name','拼音名 First Name','房号 Cabin No.','护照号码 Passport No.','出生日期 Birth of Date','性别 Gender','联系电话 Contact No. （+86）','订单负责人 AGENT','备注说明 Comment ( Charges )'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        $sheet->getStyle("A1:M1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:M1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('C0504D');
        $sheet->getStyle('A1:M1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');

        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }                

        $m_gender = [
            1 => 'M',
            2 => 'F',
        ];

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 0;

        foreach($orders as $v){
            
            //先找房间 再找人
            foreach($v->room as $room){

                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){ 
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间类型
                        $room_type = $productRoom->room->code;
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true); 
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";

                        $room_type = "";
                    } 

                    foreach($room->orderRoomCustomer as $val){   
                        $sheet->setCellValue(sprintf("A%d",$key+2),$key+1);
                       
                        $sheet->setCellValue(sprintf("E%d",$key+2),$val->orderCustomer->en_first_name);

                        $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_last_name);

                        $sheet->setCellValue(sprintf("G%d",$key+2),$room_code);

                        $sheet->setCellValue(sprintf("H%d",$key+2),$val->orderCustomer->passport);

                        $sheet->setCellValue(sprintf("I%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d-M-Y",strtotime($val->orderCustomer->birthday)) :'');

                        $sheet->setCellValue(sprintf("J%d",$key+2),$m_gender[$val->orderCustomer->gender]);
                        
                        $sheet->setCellValueExplicit(sprintf("K%d",$key+2),$val->orderCustomer->phone,\PHPExcel_Cell_DataType::TYPE_STRING);

                        $sheet->setCellValue(sprintf("L%d",$key+2),'易游');

                        $sheet->setCellValue(sprintf("M%d",$key+2),$v->company);
                                             
                        $key++;
                    }       

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                            if($customers){
                                foreach($customers as $val){   

                                  $sheet->setCellValue(sprintf("A%d",$key+2),$key+1);
                       
                                  $sheet->setCellValue(sprintf("E%d",$key+2),$val->orderCustomer->en_first_name);

                                  $sheet->setCellValue(sprintf("F%d",$key+2),$val->orderCustomer->en_last_name);

                                  $sheet->setCellValue(sprintf("G%d",$key+2),$room_code);

                                  $sheet->setCellValue(sprintf("H%d",$key+2),$val->orderCustomer->passport);

                                  $sheet->setCellValue(sprintf("I%d",$key+2),!empty($val->orderCustomer->birthday) ? date("d-M-Y",strtotime($val->orderCustomer->birthday)) :'');

                                  $sheet->setCellValue(sprintf("J%d",$key+2),$m_gender[$val->orderCustomer->gender]);
                                  
                                  $sheet->setCellValueExplicit(sprintf("K%d",$key+2),$val->orderCustomer->phone,\PHPExcel_Cell_DataType::TYPE_STRING);

                                  $sheet->setCellValue(sprintf("L%d",$key+2),'易游');

                                  $sheet->setCellValue(sprintf("M%d",$key+2),$v->company);
                                                       
                                  $key++;
                                }     

                            }
                        }


                }    
            }   

            
        }

        $sheet->getStyle(sprintf("A2:M%d",$key+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][11]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];
        return $res;
    }
    public function Template12($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $styleArray = [
            'borders' => [
                'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
            ],
        ]; //设置border

        $sheet->getStyle('A1:R1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:R1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('L')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('M')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('N')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('O')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('P')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('Q')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(20);// 设置宽度
        $sheet->getRowDimension(1)->setRowHeight(50);

        $sheet->getStyle('A1:R1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('1C1C1C');
        $sheet->getStyle('A1:R1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFAFA');


        $titleArray = ['团号','最大入住人数','舱号','目录','姓(拼音/英文)','名(拼音/英文)','性别(F/M)','出生日期(dd/mm/yyyy)','出生地(拼音/英文)','国籍(2位代码)','证件号码','签发日期(dd/mm/yyyy)','签发地(拼音/英文)','失效日(dd/mm/yyyy)','中文名','紧急联系人姓(拼音/英文)','紧急联系人名(拼音/英文)','紧急联系人联系电话(+86)'];
        $sheet->fromArray($titleArray, NULL, 'A1');



        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }

        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_3 = yii::$app->params['area']['country_code_2'];
        $key = 0;
        $m_gender = [
            1 => 'M',
            2 => 'F',
        ];
        foreach ($orders as $v) {
            $sheet->setCellValue(sprintf("A%d", $key + 2), !empty($v->assign)?$v->assign->name:'');
            $sheet->setCellValue(sprintf("P%d", $key + 2), '');
            $sheet->setCellValue(sprintf("Q%d", $key + 2), '');
            $sheet->setCellValue(sprintf("R%d", $key + 2), '');

            foreach($v->room as $room){
                if($room->stock == 1) { //计入库存
                    if ($v->product->stock == 1) {
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name . $productRoom->room->code . " " . $room->final_num . '人间';
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    } elseif ($v->product->stock == 2) {
                        $t = json_decode($v->product->rooms, true);
                        $room_name = $t[$room->final_room_id]['name'] . " " . $room->final_num . '人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";
                    }
                }
                foreach($room->orderRoomCustomer as $val)
                {
                    $sheet->setCellValue(sprintf("B%d", $key + 2), $room_num);
                    $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                    $sheet->setCellValue(sprintf("D%d", $key + 2), '');
                    $sheet->setCellValue(sprintf("E%d", $key + 2), $val->orderCustomer->en_first_name);
                    $sheet->setCellValue(sprintf("F%d", $key + 2), $val->orderCustomer->en_last_name);
                    $sheet->setCellValue(sprintf("G%d", $key + 2), $m_gender[$val->orderCustomer->gender]);
                    $sheet->setCellValue(sprintf("H%d", $key + 2), !empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');
                    $sheet->setCellValue(sprintf("I%d", $key + 2), (!empty($province[$val->orderCustomer->native]))?strtoupper($province[$val->orderCustomer->native]) : $val->orderCustomer->native);
                    $sheet->setCellValue(sprintf("J%d",$key+2),!empty($country_code_3[$val->orderCustomer->country])?$country_code_3[$val->orderCustomer->country]:$val->orderCustomer->country);
                    $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport);
                    $sheet->setCellValue(sprintf("L%d", $key + 2), !empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                    $sheet->setCellValue(sprintf("M%d",$key+2),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : $val->orderCustomer->passport_add);
                    $sheet->setCellValue(sprintf("N%d", $key + 2), !empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                    $sheet->setCellValue(sprintf("O%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);

                    $key++;
                }


                if($room->merge_id){ //拼房房间的人
                    $merge_room = $room->mergeRoom;
                    $customers = $merge_room->orderRoomCustomer;
                    if($customers){
                        foreach($customers as $val){

                            $sheet->setCellValue(sprintf("B%d", $key + 2), $room_num);
                            $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                            $sheet->setCellValue(sprintf("D%d", $key + 2), '');
                            $sheet->setCellValue(sprintf("E%d", $key + 2), $val->orderCustomer->en_first_name);
                            $sheet->setCellValue(sprintf("F%d", $key + 2), $val->orderCustomer->en_last_name);
                            $sheet->setCellValue(sprintf("G%d", $key + 2), $m_gender[$val->orderCustomer->gender]);
                            $sheet->setCellValue(sprintf("H%d", $key + 2), !empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'');
                            $sheet->setCellValue(sprintf("I%d", $key + 2), (!empty($province[$val->orderCustomer->native]))?strtoupper($province[$val->orderCustomer->native]) : $val->orderCustomer->native);
                            $sheet->setCellValue(sprintf("J%d",$key+2),!empty($country_code_3[$val->orderCustomer->country])?$country_code_3[$val->orderCustomer->country]:$val->orderCustomer->country);
                            $sheet->setCellValue(sprintf("K%d",$key+2),$val->orderCustomer->passport);
                            $sheet->setCellValue(sprintf("L%d", $key + 2), !empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                            $sheet->setCellValue(sprintf("M%d",$key+2),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : $val->orderCustomer->passport_add);
                            $sheet->setCellValue(sprintf("N%d", $key + 2), !empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                            $sheet->setCellValue(sprintf("O%d",$key+2),$val->orderCustomer->first_name.$val->orderCustomer->last_name);

                            $key++;
                        }

                    }
                }




            }
        }


        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){
            mkdir($path,0777,true);
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][12]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [
            'down_name' => $down_name.".xlsx",
            'down_url'  => $file,
        ];
        return $res;

    }

    public function Template13($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);
        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $styleArray = [
            'borders' => [
                'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
            ],
        ]; //设置border

        $sheet->getStyle('A1:J1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:J1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(40);// 设置宽度

        $sheet->getRowDimension(1)->setRowHeight(50);


        $objPHPExcel->getActiveSheet()->mergeCells('A1:J1');

        $var = '中青旅控股股份有限公司'.date("Y年m月d日",strtotime($product['go_time'])).$product['name'].'出境团队旅客名单表';
        $titleArray = [$var];
        $titleArray1 = ['序号','旅行证号','姓名','性别','出生日期','出生地','签发地','民族','家庭住址','身份证号'];
        $sheet->fromArray($titleArray, NULL, 'A1');
        $sheet->fromArray($titleArray1, NULL, 'A2');


        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }
        $key = 0;
        $key1 = 1;
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $m_gender = [
            1 => '男',
            2 => '女',
        ];


        foreach ($orders as $v) {


            foreach($v->room as $room){
                if($room->stock == 1) { //计入库存
                    if ($v->product->stock == 1) {
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name . $productRoom->room->code . " " . $room->final_num . '人间';
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    } elseif ($v->product->stock == 2) {
                        $t = json_decode($v->product->rooms, true);
                        $room_name = $t[$room->final_room_id]['name'] . " " . $room->final_num . '人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";
                    }
                }
                foreach($room->orderRoomCustomer as $val)
                {
                    $sheet->setCellValue(sprintf("A%d", $key + 3), $key1);
                    $sheet->setCellValueExplicit(sprintf("B%d",$key+3),$val->orderCustomer->passport);
                    $sheet->setCellValue(sprintf("C%d", $key + 3), $val->orderCustomer->first_name.$val->orderCustomer->last_name);
                    $sheet->setCellValue(sprintf("D%d", $key + 3), $m_gender[$val->orderCustomer->gender]);
                    $sheet->setCellValue(sprintf("E%d", $key + 3), !empty($val->orderCustomer->birthday) ? date("Y-m-d",strtotime($val->orderCustomer->birthday)) :'');
                    $sheet->setCellValue(sprintf("F%d", $key + 3), $val->orderCustomer->native);
                    $sheet->setCellValue(sprintf("G%d", $key + 3), $val->orderCustomer->passport_add);
                    $sheet->setCellValue(sprintf("H%d", $key + 3), $val->orderCustomer->nation);
                    $sheet->setCellValue(sprintf("I%d", $key + 3), $val->orderCustomer->card_address);
                    $sheet->setCellValueExplicit(sprintf("J%d",$key+3),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);


                    $key++;
                    $key1++;

                }


                if($room->merge_id){ //拼房房间的人
                    $merge_room = $room->mergeRoom;
                    $customers = $merge_room->orderRoomCustomer;
                    if($customers){
                        foreach($customers as $val){

                            $sheet->setCellValue(sprintf("A%d", $key + 3), $key1);
                            $sheet->setCellValueExplicit(sprintf("B%d",$key+3),$val->orderCustomer->passport);
                            $sheet->setCellValue(sprintf("C%d", $key + 3), $val->orderCustomer->first_name.$val->orderCustomer->last_name);
                            $sheet->setCellValue(sprintf("D%d", $key + 3), $m_gender[$val->orderCustomer->gender]);
                            $sheet->setCellValue(sprintf("E%d", $key + 3), !empty($val->orderCustomer->birthday) ? date("Y-m-d",strtotime($val->orderCustomer->birthday)) :'');
                            $sheet->setCellValue(sprintf("F%d", $key + 3), $val->orderCustomer->native);
                            $sheet->setCellValue(sprintf("G%d", $key + 3), $val->orderCustomer->passport_add);
                            $sheet->setCellValue(sprintf("H%d", $key + 3), $val->orderCustomer->nation);
                            $sheet->setCellValue(sprintf("I%d", $key + 3), $val->orderCustomer->native);
                            $sheet->setCellValueExplicit(sprintf("J%d",$key+3),$val->orderCustomer->card,\PHPExcel_Cell_DataType::TYPE_STRING);


                            $key++;
                            $key1++;
                        }

                    }
                }




            }
        }



        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){
            mkdir($path,0777,true);
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][13]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [
            'down_name' => $down_name.".xlsx",
            'down_url'  => $file,
        ];
        return $res;


    }

    public function Template14($product_id,$type)
    {
        if(empty($product_id)){
            return false;
        }
        $product = Product::findOne($product_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('辉煌号名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $styleArray = [
            'borders' => [
                'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
            ],
        ]; //设置border
        $sheet->setCellValue('A1',' 订舱信息检查');

        $objPHPExcel->getActiveSheet()->mergeCells('A1:A3');

        $sheet->getColumnDimension('A')->setWidth(35);// 设置宽度
        $sheet->getColumnDimension('P')->setWidth(30);// 设置宽度
        $sheet->getColumnDimension('Q')->setWidth(14);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(15);// 设置宽度

        $titleArray = ['舱型','舱号','最大入住人数','团号','姓','名','性别','生日','国籍','护照号码','签发日期','有效日期','出生地','签发地','中文名','身份证 回乡证/台胞证','客人手机号码','旅行社紧急联系人','旅行社紧急联系方式'];
        $sheet->fromArray($titleArray, NULL, 'A4');


        if($type == 3){ //全部订单
            $orders = Order::find()->where(['product_id'=>$product_id])->andWhere(['<=','status',4])->all();
        }else if($type == 2){ //已付款订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>3])->all();
        }else if($type == 1){ //已审核订单
            $orders = Order::find()->where(['product_id'=>$product_id,'check'=>1])->andWhere(['<=','status',3])->all();
        }else if($type == 4){ //已提交订单
            $orders = Order::find()->where(['product_id'=>$product_id,'status'=>4])->all();
        }

        $country_code_3 = yii::$app->params['area']['country_code_3'];
        $key = 4;

        foreach($orders as $v){

            //先找房间 再找人
            foreach($v->room as $room){
                if($room->stock == 1){ //计入库存
                    if($v->product->stock == 1){
                        $productRoom = ProductRoom::findOne($room->final_room_id);
                        //房间名称
                        $room_name = $productRoom->room->name.$productRoom->room->code." ".$room->final_num.'人间';
                        //房间号
                        $room_code = $room->code;
                        //最大入住人数
                        $room_num = $productRoom->room->num;
                    }elseif($v->product->stock == 2){
                        $t = json_decode($v->product->rooms, true);
                        $room_name = $t[$room->final_room_id]['name']." ".$room->final_num.'人';
                        $room_code = $room->code;
                        //最大入住人数 为空
                        $room_num = "";
                    }
                    foreach($room->orderRoomCustomer as $val){
                        $birthday = !empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'';
                        $sheet->setCellValue(sprintf("A%d",$key+1),$room_name);
                        $sheet->setCellValue(sprintf("B%d",$key+1),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $sheet->setCellValueExplicit(sprintf("C%d",$key+1),$room_num);
                        $sheet->setCellValue(sprintf("D%d",$key+1),($v->assign)?$v->assign->name:'');
                        $sheet->setCellValue(sprintf("E%d",$key+1),$val->orderCustomer->en_first_name);
                        $sheet->setCellValue(sprintf("F%d",$key+1),$val->orderCustomer->en_last_name);
                        $sheet->setCellValue(sprintf("G%d",$key+1),yii::$app->params['gender_en'][$val->orderCustomer->gender]);
                        $sheet->setCellValue(sprintf("H%d",$key+1),$birthday);
                        $sheet->setCellValue(sprintf("I%d",$key+1),!empty($country_code_3[$val->orderCustomer->country])?$country_code_3[$val->orderCustomer->country]:$val->orderCustomer->country);
                        $sheet->setCellValue(sprintf("J%d",$key+1),$val->orderCustomer->passport);
                        $sheet->setCellValue(sprintf("K%d",$key+1),!empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                        $sheet->setCellValue(sprintf("L%d",$key+1),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                        $sheet->setCellValue(sprintf("M%d",$key+1),(!empty($province[$val->orderCustomer->native]))?strtoupper($province[$val->orderCustomer->native]) : $val->orderCustomer->native);
                        $sheet->setCellValue(sprintf("N%d",$key+1),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : $val->orderCustomer->passport_add);
                        $sheet->setCellValue(sprintf("O%d",$key+1),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                        $sheet->setCellValue(sprintf("P%d",$key+1),$val->orderCustomer->card);
                        $sheet->setCellValue(sprintf("Q%d",$key+1),$val->orderCustomer->phone);
                        $sheet->setCellValue(sprintf("R%d",$key+1),$v->username);
                        $sheet->setCellValueExplicit(sprintf("S%d",$key+1),$v->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
                        $key++;
                    }

                    if($room->merge_id){ //拼房房间的人
                        $merge_room = $room->mergeRoom;
                        $customers = $merge_room->orderRoomCustomer;
                        if($customers){
                            foreach($customers as $val){
                                $birthday = !empty($val->orderCustomer->birthday) ? date("d/m/Y",strtotime($val->orderCustomer->birthday)) :'';
                                $sheet->setCellValue(sprintf("A%d",$key+1),$room_name);
                                $sheet->setCellValue(sprintf("B%d",$key+1),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                                $sheet->setCellValueExplicit(sprintf("C%d",$key+1),$room_num);
                                $sheet->setCellValue(sprintf("D%d",$key+1),($v->assign)?$v->assign->name:'');
                                $sheet->setCellValue(sprintf("E%d",$key+1),$val->orderCustomer->en_first_name);
                                $sheet->setCellValue(sprintf("F%d",$key+1),$val->orderCustomer->en_last_name);
                                $sheet->setCellValue(sprintf("G%d",$key+1),yii::$app->params['gender_en'][$val->orderCustomer->gender]);
                                $sheet->setCellValue(sprintf("H%d",$key+1),$birthday);
                                $sheet->setCellValue(sprintf("I%d",$key+1),!empty($country_code_3[$val->orderCustomer->country])?$country_code_3[$val->orderCustomer->country]:$val->orderCustomer->country);
                                $sheet->setCellValue(sprintf("J%d",$key+1),$val->orderCustomer->passport);
                                $sheet->setCellValue(sprintf("K%d",$key+1),!empty($val->orderCustomer->passport_grant) ? date("d/m/Y",strtotime($val->orderCustomer->passport_grant)) :'');
                                $sheet->setCellValue(sprintf("L%d",$key+1),!empty($val->orderCustomer->passport_date) ? date("d/m/Y",strtotime($val->orderCustomer->passport_date)) :'');
                                $sheet->setCellValue(sprintf("M%d",$key+1),(!empty($province[$val->orderCustomer->native]))?strtoupper($province[$val->orderCustomer->native]) : $val->orderCustomer->native);
                                $sheet->setCellValue(sprintf("N%d",$key+1),(!empty($province[$val->orderCustomer->passport_add]))?strtoupper($province[$val->orderCustomer->passport_add]) : $val->orderCustomer->passport_add);
                                $sheet->setCellValue(sprintf("O%d",$key+1),$val->orderCustomer->first_name.$val->orderCustomer->last_name);
                                $sheet->setCellValue(sprintf("P%d",$key+1),$val->orderCustomer->card);
                                $sheet->setCellValue(sprintf("Q%d",$key+1),$val->orderCustomer->phone);
                                $sheet->setCellValue(sprintf("R%d",$key+1),$v->username);
                                $sheet->setCellValueExplicit(sprintf("S%d",$key+1),$v->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
                                $key++;
                            }

                        }
                    }


                }
            }

        }
        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/excel/'.date('Ym').'/';
        if(!is_dir($path)){
            mkdir($path,0777,true);
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $down_name = sprintf("%s_%s_%s",$product->name,$product->go_time,yii::$app->params['template'][14]);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = str_replace("&","",$down_name);  //删除&
        $down_name = preg_replace("/\s/","",$down_name); //去除空格
        $res = [
            'down_name' => $down_name.".xlsx",
            'down_url'  => $file,
        ];
        return $res;



    }


}
